slow data refresh with the below query

[NamrataParekh]

let
Source = #"Materiallist Data (2)",
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Valuation Class", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Material type"}),
#"Merged Queries2" = Table.NestedJoin(#"Removed Columns", {"Material"}, #"Movements Data (2)", {"Material"}, "Movements Data (2)", JoinKind.LeftOuter),
#"Expanded Movements Data (2)" = Table.ExpandTableColumn(#"Merged Queries2", "Movements Data (2)", {"CQ 202304", "CV 202304", "CQ 202303", "CV 202303", "CQ 202302", "CV 202302", "CQ 202301", "CV 202301", "CQ 202212", "CV 202212", "CQ 202211", "CV 202211", "CQ 202210", "CV 202210", "CQ 202209", "CV 202209", "CQ 202208", "CV 202208", "CQ 202207", "CV 202207", "CQ 202206", "CV 202206", "CQ 202205", "CV 202205", "CQ 202204", "CV 202204", "CQ 202203", "CV 202203", "CQ 202202", "CV 202202", "CQ 202201", "CV 202201", "CQ 202112", "CV 202112", "CQ 202111", "CV 202111", "CQ 202110", "CV 202110", "CQ 202109", "CV 202109", "CQ 202108", "CV 202108", "CQ 202107", "CV 202107", "CQ 202106", "CV 202106", "CQ 202105", "CV 202105", "CQ 202104", "CV 202104", "CQ 202103", "CV 202103", "CQ 202102", "CV 202102", "CQ 202101", "CV 202101"}, {"CQ 202304", "CV 202304", "CQ 202303", "CV 202303", "CQ 202302", "CV 202302", "CQ 202301", "CV 202301", "CQ 202212", "CV 202212", "CQ 202211", "CV 202211", "CQ 202210", "CV 202210", "CQ 202209", "CV 202209", "CQ 202208", "CV 202208", "CQ 202207", "CV 202207", "CQ 202206", "CV 202206", "CQ 202205", "CV 202205", "CQ 202204", "CV 202204", "CQ 202203", "CV 202203", "CQ 202202", "CV 202202", "CQ 202201", "CV 202201", "CQ 202112", "CV 202112", "CQ 202111", "CV 202111", "CQ 202110", "CV 202110", "CQ 202109", "CV 202109", "CQ 202108", "CV 202108", "CQ 202107", "CV 202107", "CQ 202106", "CV 202106", "CQ 202105", "CV 202105", "CQ 202104", "CV 202104", "CQ 202103", "CV 202103", "CQ 202102", "CV 202102", "CQ 202101", "CV 202101"}),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded Movements Data (2)",null,0,Replacer.ReplaceValue,{"SQ 202112", "SV 202112", "SQ 202201", "SV 202201", "SQ 202102", "SV 202102", "SQ 202202", "SV 202202", "SQ 202103", "SV 202103", "SQ 202203", "SV 202203", "SQ 202104", "SV 202104", "SQ 202204", "SV 202204", "SQ 202105", "SV 202105", "SQ 202205", "SV 202205", "SQ 202106", "SV 202106", "SQ 202206", "SV 202206", "SQ 202107", "SV 202107", "SQ 202207", "SV 202207", "SQ 202108", "SV 202108", "SQ 202208", "SV 202208", "SQ 202109", "SV 202109", "SQ 202209", "SV 202209", "SQ 202110", "SV 202110", "SQ 202111", "SV 202111", "SQ 202212", "SV 202212", "SQ 202210", "SV 202210", "SQ 202211", "SV 202211", "SQ 202301", "SV 202301", "SQ 202302", "SV 202302"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"CQ 202203", "CV 202203", "CQ 202201", "CV 202201", "CQ 202108", "CV 202108", "CQ 202107", "CV 202107", "CQ 202105", "CV 202105", "CQ 202106", "CV 202106", "CQ 202104", "CV 202104", "CQ 202103", "CV 202103", "CQ 202102", "CV 202102", "CQ 202208", "CV 202208", "CQ 202207", "CV 202207", "CQ 202206", "CV 202206", "CQ 202205", "CV 202205", "CQ 202204", "CV 202204", "CQ 202202", "CV 202202", "CQ 202112", "CV 202112", "CQ 202111", "CV 202111", "CQ 202110", "CV 202110", "CQ 202109", "CV 202109", "CQ 202212", "CV 202212", "CQ 202211", "CV 202211", "CQ 202210", "CV 202210", "CQ 202209", "CV 202209", "CQ 202301", "CV 202301", "CQ 202302", "CV 202302", "CQ 202303", "CV 202303", "CQ 202304", "CV 202304", "CQ 202101", "CV 202101"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value2", {"Valuation Class"}, #"Valuation Class", {"Valuation Class"}, "Valuation Class.1", JoinKind.LeftOuter),
#"Expanded Valuation Class.1" = Table.ExpandTableColumn(#"Merged Queries", "Valuation Class.1", {"Valuation Class Description"}, {"Valuation Class Description"})
in
#"Expanded Valuation Class.1"


let
Source = Append1,
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Material", "Valuation Class", "Valuation Class Description"}, "Attribute", "Value"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"Attribute.2"}, #"LTM period mapping", {"LTM period"}, "LTM period mapping", JoinKind.LeftOuter),
#"Expanded LTM period mapping" = Table.ExpandTableColumn(#"Merged Queries", "LTM period mapping", {"LTM_no period"}, {"LTM_no period"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded LTM period mapping",null,"0",Replacer.ReplaceValue,{"LTM_no period"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute.2", "Period"}, {"LTM_no period", "LTM Period"}}),
#"Merged Columns" = Table.CombineColumns(#"Renamed Columns",{"Attribute.1", "LTM Period"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Period"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Merged] <> "CQ 0" and [Merged] <> "CV 0" and [Merged] <> "SQ 0" and [Merged] <> "SV 0")),
#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Merged]), "Merged", "Value", List.Sum),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "LTM Total SV", each [SV 12]+[SV 11]+[SV 10]+[SV 09]+[SV 08]+[SV 07]+[SV 06]+[SV 05]+[SV 04]+[SV 03]+[SV 02]+[SV 01]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "LTM Total SQ", each [SQ 12]+[SQ 11]+[SQ 10]+[SQ 09]+[SQ 08]+[SQ 07]+[SQ 06]+[SQ 05]+[SQ 04]+[SQ 03]+[SQ 02]+[SQ 01]),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "LTM Total CV", each [CV 12]+[CV 11]+[CV 10]+[CV 09]+[CV 08]+[CV 07]+[CV 06]+[CV 05]+[CV 04]+[CV 03]+[CV 02]+[CV 01]),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "LTM Total CQ", each [CQ 12]+[CQ 11]+[CQ 10]+[CQ 09]+[CQ 08]+[CQ 07]+[CQ 06]+[CQ 05]+[CQ 04]+[CQ 03]+[CQ 02]+[CQ 01]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"LTM Total SV", type number}, {"LTM Total SQ", type number}, {"LTM Total CV", type number}, {"LTM Total CQ", type number}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type", "LTM Avg SV", each [LTM Total SV]/12),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "LTM Avg SQ", each [LTM Total SQ]/12),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "LTM Avg CV", each [LTM Total CV]/12),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "LTM Avg CQ", each [LTM Total CQ]/12),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom7",{{"LTM Avg SV", type number}, {"LTM Avg SQ", type number}, {"LTM Avg CV", type number}, {"LTM Avg CQ", type number}}),
#"Added Custom8" = Table.AddColumn(#"Changed Type1", "Std Dev", each List.StandardDeviation({[CQ 12],[CQ 11],[CQ 10],[CQ 09],[CQ 08],[CQ 07],[CQ 06],[CQ 05],[CQ 04],[CQ 03],[CQ 02],[CQ 01]})),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom8",{{"Std Dev", type number}}),
#"Added Custom9" = Table.AddColumn(#"Changed Type2", "Variance", each if (Number.IsNaN([Std Dev]/[LTM Avg CQ])) then 0 else (-[Std Dev]/[LTM Avg CQ])),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom9",{{"Variance", type number}}),
#"Added Custom10" = Table.AddColumn(#"Changed Type3", "DIO", each (([LTM Avg CV]/[LTM Avg SV])*-30)),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom10",{{"DIO", Int64.Type}}),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type4", {{"DIO", 0}}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Errors",null,0,Replacer.ReplaceValue,{"DIO"}),
#"Added Index" = Table.AddIndexColumn(#"Replaced Value1", "Index", 1, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"LTM Avg CV", Order.Ascending}}),
#"Added Custom12" = Table.AddColumn(#"Sorted Rows", "BufferedValues", each List.Buffer(#"Sorted Rows" [LTM Avg CV])),
#"Added Custom11" = Table.AddColumn(#"Added Custom12", "Custom", each ...)
in
#"Added Custom11"

[lbendlin]

ok. What is your question?

[NamrataParekh]

After running these 2 querys the data refresh is too slow and when I try and add new columns to the second query it gets stuck. What should I do?

[v-stephen-msft]

Hi @NamrataParekh ,

Slow data refresh in Power Query and stuck when adding new custom columns, you can try the following methods to resolve:

Refine the query: Check each step in the query to ensure that they are necessary. Remove steps that you don't need and merge steps that can be merged to reduce query complexity.

Avoid time-consuming operations: Some operations, such as merging queries, expanding columns, and grouping, can cause performance degradation. Minimize the use of these operations or, if necessary, use more efficient alternatives.

Reduce the amount of data: When loading data, only the columns and rows you need are preserved. This can be done by removing unwanted columns, filtering rows, or aggregating data.

Turn off background data loading: In Power Query Editor, tap File> Options and Settings> Query Options, and then uncheck the Background data loading option. This will allow data refresh to occur in the foreground, potentially improving performance.

Try incremental refresh: If you're using a Power BI Premium or Power BI Pro subscription, you can try the incremental refresh feature. This way, only new or changed data is refreshed, not the entire dataset.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.