Hello everyone,
I'm new to PQ and M code in general, so the code below will look very messy. However, I'm trying to improve the speed at which the query runs because I don't expect this to be super complex stuff, but yet somehow takes a good 5-10 minutes to run.
I have been looking at query optimization techniques but I'm not expert enough to actually understand what / where I should use each so I'm felling a bit lost and maybe your support could shade some light on it.
Thank you!
let
Source = Excel.Workbook(File.Contents("C:\Users\D\Data Table.xlsx"), null, true),
#"Sheet 1_Sheet" = Source{[Item="Sheet 1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Sheet 1_Sheet", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Column1", "Column2", "Column3", "Net Revenue"}),
#"Filled Down" = Table.FillDown(#"Removed Other Columns",{"Column1", "Column2"}),
#"Removed Top Rows" = Table.Skip(#"Filled Down",1),
#"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows",{{"Column1", "Customer Type"}, {"Column2", "Category Name"}, {"Column3", "SKU"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Category Name] = "Mattresses")),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Customer Type", "Category Name", "SKU"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Customer Type", type text}, {"Category Name", type text}, {"SKU", type text}, {"Attribute", type text}, {"Value", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Country", each "FR"),
#"Sorted Rows" = Table.Sort(#"Added Custom",{{"Value", Order.Descending}}),
#"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Customer Type", "Category Name", "SKU", "Attribute", "Value", "Country"}),
#"Added Index1" = Table.AddIndexColumn(#"Reordered Columns", "Index.1", 1, 1, Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Added Index1",{{"Index.1", "Index"}}),
#"Inserted Text Length" = Table.AddColumn(#"Renamed Columns1", "Length", each Text.Length([SKU]), Int64.Type),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Text Length",{"Length"}),
#"Inserted Text Range" = Table.AddColumn(#"Removed Columns", "Text Range", each Text.Middle([SKU], 5, 6), type text),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Text Range",{{"Text Range", "Size"}}),
#"Counted Rows" = Table.RowCount(#"Renamed Columns2"),
Custom1 = #"Renamed Columns2",
#"Added Custom1" = Table.AddColumn(Custom1, "Custom", each [Index]/#"Counted Rows"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", Percentage.Type}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type2",{"Customer Type", "Category Name", "SKU", "Attribute", "Value", "Country", "Index", "Custom", "Size"}),
BufferedValues = List.Buffer(#"Reordered Columns1"[Value]),
RT = Table.FromList(fxRunningTotal(BufferedValues),Splitter.SplitByNothing(),{"RunningTota"}),
Columns = List.Combine({Table.ToColumns(#"Reordered Columns1"),Table.ToColumns(RT)}),
#"Converted" = Table.FromColumns(Columns,List.Combine({Table.ColumnNames(#"Reordered Columns1"),{"Running Total"}})),
#"Changed Type1" = Table.TransformColumnTypes(Converted,{{"Running Total", Int64.Type}}),
#"Reordered Columns2" = Table.ReorderColumns(#"Changed Type1",{"Customer Type", "Category Name", "SKU", "Attribute", "Value", "Running Total", "Country", "Index", "Custom", "Size"}),
#"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns2",{{"Value", "Net Revenue"}}),
#"Reordered Columns3" = Table.ReorderColumns(#"Renamed Columns3",{"Country", "Category Name", "Customer Type", "SKU", "Net Revenue", "Attribute", "Running Total", "Index", "Custom", "Size"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns3",{"Attribute"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"Running Total", "Accumulated Sales"}, {"Index", "Acc. Items"}, {"Custom", "% Acc. Items"}}),
#"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns4",{"Size"}),
#"Calculated Sum" = List.Sum(#"Removed Columns2"[Net Revenue]),
Custom2 = #"Removed Columns2",
#"Added Custom2" = Table.AddColumn(Custom2, "Custom.1", each [Accumulated Sales]/#"Calculated Sum"),
#"Reordered Columns4" = Table.ReorderColumns(#"Added Custom2",{"Country", "Category Name", "Customer Type", "SKU", "Net Revenue", "Accumulated Sales", "Custom.1", "Acc. Items", "% Acc. Items"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Reordered Columns4",{{"Custom.1", Percentage.Type}}),
#"Renamed Columns5" = Table.RenameColumns(#"Changed Type3",{"Custom.1", "% Accumulated Sales"}),
#"Added Conditional Column" = Table.AddColumn(#"Renamed Columns5", "Custom", each if [#"% Accumulated Sales"] <= 0.8 then "A" else if [#"% Accumulated Sales"] <= 0.95 then "B" else if [#"% Accumulated Sales"] <= 1 then "C" else if [#"% Accumulated Sales"] > 1 then "C" else null),
#"Renamed Columns6" = Table.RenameColumns(#"Added Conditional Column",{{"Custom", "Class"}}),
#"Added Custom3" = Table.AddColumn(#"Renamed Columns6", "Custom", each #"Renamed Columns6" [Class] {[Acc. Items]-2}),
#"Renamed Columns7" = Table.RenameColumns(#"Added Custom3",{{"Custom", "Class Anterior"}}),
#"Added Conditional Column1" = Table.AddColumn(#"Renamed Columns7", "Custom", each if [Class] = [Class Anterior] then 0 else if [Class Anterior] <> [Class] then [Accumulated Sales] else null),
#"Removed Columns3" = Table.RemoveColumns(#"Added Conditional Column1",{"Class Anterior"}),
#"Reordered Columns5" = Table.ReorderColumns(#"Removed Columns3",{"Country", "Category Name", "Customer Type", "SKU", "Net Revenue", "Accumulated Sales", "% Accumulated Sales", "Acc. Items", "% Acc. Items", "Custom", "Class"}),
#"Inserted Last Characters" = Table.AddColumn(#"Reordered Columns5", "Last Characters", each Text.End([SKU], 3), type text),
#"Renamed Columns8" = Table.RenameColumns(#"Inserted Last Characters",{{"Last Characters", "Cover Rotation"}}),
#"Inserted First Characters" = Table.AddColumn(#"Renamed Columns8", "First Characters", each Text.Start([SKU], 5), type text),
#"Renamed Columns9" = Table.RenameColumns(#"Inserted First Characters",{{"First Characters", "Product Type"}}),
#"Merged Columns" = Table.CombineColumns(#"Renamed Columns9",{"Product Type", "Cover Rotation"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Product Rotation")
in
#"Merged Columns"
