Hello,
where can I fasten up my Query? Where excactly do I user Table.Buffer() or Table.AddKey()?
In Short I want to concatonade tables from different workbooks. Restriction: only workbooks where the date (which is in a seperate table) is not expired - see #"Get Ablaufdatum" and #"Aktiv oder inaktiv". In total i retrive 900 rows out of 40 Excel files.
I commented the M file so its easier to follow. Hope its clear.
let
Source = Folder.Files("K:\XXXXXXX"),
// Without Subfolder (temp files excluded)
OnlyMainFolder = Table.AddColumn(Source, "Main Folder", each if (Text.Length([Folder Path]) < 47 and not Text.StartsWith([Name],"~$")) then "Main Folder" else null),
ShowOnlyMaintFolder = Table.SelectRows(OnlyMainFolder, each ([Main Folder] = "Main Folder") and ([Folder Path] <> "K:\FIN@KRE\EIB neu\Globaldarl-Übersicht\Gd 1\") and ([Extension] = ".xls" or [Extension] = ".xlsm" or [Extension] = ".xlsx")),
//in order to get to my table "Datum" where the date is located (needed for expired or not)
GetAllTables = Table.AddColumn(ShowOnlyMaintFolder, "AllTables", each Excel.Workbook(File.Contents([Folder Path]&[Name]), true, true)),
//get the Date
#"Get Ablaufdatum" = Table.AddColumn(GetAllTables, "AblDatum", each [AllTables]{[Name="Datum"]}[Data]{0}[Ablaufdatum]),
#"Changed Type" = Table.TransformColumnTypes(#"Get Ablaufdatum",{{"AblDatum", type date}}),
//in order to later filter active or inactive
#"Aktiv oder inaktiv" = Table.AddColumn(#"Changed Type", "aktiv/inaktiv", each if [AblDatum] >= DateTime.Date(DateTime.LocalNow()) then "its active" else "its expired"),
#"Nur aktive anzeigen" = Table.SelectRows(#"Aktiv oder inaktiv", each ([#"aktiv/inaktiv"] = "its active")),
//from here onwards the M code starts the "concatination Process" with the Sample File
#"Filtered Hidden Files1" = Table.SelectRows(#"Nur aktive anzeigen", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File (3)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File (3)"(#"Sample File (3)"))),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Table Column1", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"12", "Origin"}}),
//there are multiple Colums to be renamed, but this could make the M code longer
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Origin", type text}, {"Nr", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Nr] <> null and [Nr] <> "Nr"))
in
#"Filtered Rows"
Kind regards
Max
