Performance issues (M code included)

[Anonymous]

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

[Anonymous · Accepted solution]

Hi @Anonymous ,

M functions are a more hands-on language and it is hard to say without actual data support. Please consider making the load with as little data as possible, removing unneeded rows, filtering columns, etc.

Please see if this helps:

Performance Tip: Partition your tables at crossjoins where possible

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data