BalloutMoe
Board Regular
- Joined
- Jun 4, 2021
- Messages
- 137
- Office Version
- 365
- Platform
- Windows
Below I have a code that gets pdf tables and filters and merges however it is taking a lot of time to get this done, anyway to help speed this up?
Power Query:
let
Source = Folder.Files(FolderPath),
#"FilteredCSV-ReturnFldr" = Table.AddColumn( Table.SelectRows( Table.SelectRows(Source, each ([Extension] = ".pdf")), each not Text.Contains([Folder Path], "Return")), "Custom", each Pdf.Tables([Content])),
#"Expanded Custom" = Table.SelectRows( Table.ExpandTableColumn(#"FilteredCSV-ReturnFldr", "Custom", {"Kind", "Data"}, {"Custom.Kind", "Custom.Data"}),each ([Custom.Kind] = "Table")),
#"Added Custom1" = Table.SelectRows( Table.AddColumn(#"Expanded Custom", "Custom", each Table.Contains( [Custom.Data], [Column2 = "QTY"])), each ([Custom] = true)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each Table.RemoveFirstN( [Custom.Data], 1)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each Table.PromoteHeaders([Custom.1])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Custom.2", "Name"}),
#"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.2", ExpandColumns, ExpandColumns),
#"Merged Columns" = Table.CombineColumns(#"Expanded Custom.2",ColumnNames,Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Description"),
#"Filtered Rows1" = Table.SelectRows(#"Merged Columns", each ([QTY] <> null))
in
#"Filtered Rows1"