Power Query Load to Data Model Failed

[Anonymous]

Hi Experts,

I am building a model and have run into an issue with one query whereby if I select load to the Data Model the load will just hang requiring a PC reboot. The query refreshes perfectly within Power Query and will load to a Table in Excel. I can also then load from that Table into the Data Model. A similar query in the same model also loads without an issue although that does use an intermediate Table for further input. 

I have rewritten the query but with the same result.

Could the problem be caused by merging data from one (Excel) table and then using the result to merge to another (Excel) table? I can see that such 'nesting' may be an issue but would expect the query to error rather than not just load.

The only other possibility I can think of is that one of the data sources is held in SharePoint with organisational access so possibly a privacy level problem. Any ideas are welcome - thank you!

let
    Source = TBL_Info,
    #"Added Custom" = Table.AddColumn(Source, "WBS", each TBL_WBS),
    #"Expanded WBS" = Table.ExpandTableColumn(#"Added Custom", "WBS", {"WBS"}, {"WBS_Ext"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded WBS", "WBS", each [AR]&"-"&[WBS_Ext]),
 
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each TBL_Mth),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom2", "Custom", {"FPd"}, {"FMth"}),
 
    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"WBS", "FMth"}, CJI3_Sum, {"Object", "Period"}, "CJI3_Sum", JoinKind.LeftOuter),
    #"Expanded CJI3_Sum" = Table.ExpandTableColumn(#"Merged Queries", "CJI3_Sum", {"Rep_Value"}, {"Rep_Value"}),
 
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded CJI3_Sum", {{"FMth", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded CJI3_Sum", {{"FMth", type text}}, "en-GB")[FMth]), "FMth", "Rep_Value", List.Sum),
 
    #"Merged Queries1" = Table.NestedJoin(#"Pivoted Column", {"WBS_Ext"}, TBL_WBS, {"WBS"}, "TBL_WBS", JoinKind.LeftOuter),
    #"Expanded TBL_WBS" = Table.ExpandTableColumn(#"Merged Queries1", "TBL_WBS", {"Category"}, {"Category"}),
    
    #"Merged Queries2" = Table.NestedJoin(#"Expanded TBL_WBS", {"Category"}, TBL_Cat, {"Category"}, "TBL_Cat", JoinKind.LeftOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries2", "TBL_Cat", {"Cat_Sort"}, {"Cat_Sort"}),

    #"Grouped Rows" = Table.Group(#"Expanded Table", {"QB ID","Project ID","Project Name", "AR","Category", "Cat_Sort", "Approved CAP","Approved PPD","Approved PRE","Approved IntLab"}, {{"A1", each List.Sum([1]), type nullable number}, {"A2", each List.Sum([2]), type nullable number}, {"A3", each List.Sum([3]), type nullable number}, {"A4", each List.Sum([4]), type nullable number},{"A5", each List.Sum([5]), type nullable number},{"A6", each List.Sum([6]), type nullable number},{"A7", each List.Sum([7]), type nullable number},{"A8", each List.Sum([8]), type nullable number},{"A9", each List.Sum([9]), type nullable number},{"A10", each List.Sum([10]), type nullable number},{"A11", each List.Sum([11]), type nullable number},{"A12", each List.Sum([12]), type nullable number}}),
  
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"QB ID", Order.Ascending}, {"Cat_Sort", Order.Ascending}}),

    #"Added Custom3" = Table.AddColumn(#"Sorted Rows", "Custom", each TBL_FXFC),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom3", "Custom", {"Future LC_USD"}, {"LC|USD"}),
    #"Added Custom4" = Table.AddColumn(#"Expanded Custom1", "App CAP USD", each [Approved CAP]/[#"LC|USD"], type nullable number),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "App PPD USD", each [Approved PPD]/[#"LC|USD"], type nullable number),
    #"Added Custom6" = Table.AddColumn(#"Added Custom5", "App PRE USD", each [Approved PRE]/[#"LC|USD"], type nullable number),
    #"Added Custom7" = Table.AddColumn(#"Added Custom6", "App ILPE USD", each [Approved IntLab]/[#"LC|USD"], type nullable number)

in
    #"Added Custom7"

[Anonymous]

So, after recreating line by line I discovered that today the query loads without a problem. Guess there must have been another factor affecting the performance last week. I have noticed that if I close Excel and re-open the performance also improves so will investigate why this is.

[lbendlin · Accepted solution]

Use Query Diagnostics to identify the steps that are causing your query to be slow and/or run out of resources.

Merging should be avoided wherever possible. Let the data model do that work for you.

[Anonymous]

Thanks for the help. Unfortunately I don't have admin rights to use diagnostics but interesting to know that the data model is the better way to combine tables. I still don't understand, however, why the query works perfectly well in the editor and to load to an Excel table (takes a few seconds) but won't load to the data model. I turned off the option to create relationships when adding to the data model - can you advise what other difference may be causing the issue? In the meantime I'll manually work through the query to see which step causes the issue.