Início / user-22371509

chi005's questions

Martin Hope
chi005
Asked: 2024-04-10 13:11:09 +0800 CST
  • 5

Tenho este powerquery que desejo extrair os valores destacados na linha 5 para outra coluna. Eu posso usar facilmente

Record.Field(#"Added Custom"{4},"Column17")

Mas o problema que tenho é que esse valor pode aparecer em qualquer lugar da coluna 12 à coluna 20, mas ainda na linha 5. Como posso encontrá-lo nessa linha para extraí-lo. Obrigado. insira a descrição da imagem aqui

powerquery
Martin Hope
chi005
Asked: 2023-08-22 00:09:27 +0800 CST
  • 5

Eu tenho este código do Power Query abaixo que funciona muito bem na leitura de PDFs e na saída das colunas que desejo. Como posso modificá-lo para ler uma pasta que contém arquivos PDF do mesmo formato e percorrer todos os arquivos PDF. A pasta pode conter até 300 arquivos.

Obrigado pela sua ajuda antecipada

let
    Source = Pdf.Tables(File.Contents("C:\Users\MyReport.pdf"), [Implementation="1.3"]),
    Page1 = Source{[Id="Page001"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Page1, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"[image]", type text}, {"TestHeader", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column5", "Column6", "Column8", "Column10"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each not Text.StartsWith([#"TestHeader"], "MyOff")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"[image]", "Column2"}, {"TestHeader", "Column3"}, {"Column7", "Column5"}, {"Column9", "Column6"}}),
    Page2 = Source{[Id="Page002"]}[Data],
    #"Changed Type2" = Table.TransformColumnTypes(Page2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Column5"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column6", "Column5"}, {"Column7", "Column6"}}),
    #"Appended Query" = Table.Combine({#"Renamed Columns", #"Renamed Columns2"}),
    
    #"Added Custom" = Table.AddColumn(#"Appended Query", "Test_Date", each Record.Field(#"Appended Query"{0},"Column3")),
    
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each Text.StartsWith([Column1], "NF")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows1",{{"Column1", "Wells"}, {"Column2", "Wet"}, {"Column3", "Hours"}, {"Column4", "FTHP_p"}, {"Column5", "FTHP_b"}, {"Column6", "Comments"}})
in
    #"Renamed Columns1"
powerquery