Reply
Member
Posts: 74
Registered: ‎07-27-2017

Loading Data to Model Takes Ages

If I make a change to any of my queries, the process of evaluating the transition and "loading the data to model" can take a very long time - typically 10-15 minutes for a single table.

 

Some pertinent information:

  • Data is a web connection to XML
  • There are 800 rows in the original dataset
  • There is one merge to a table with 28k rows, using a unique ID field (19 digits stored as text)
  • There are several calculated columns where the result of one or more calculations is used in the next calculated column (I'm thinking a function to do the whole thing in one step would help)
  • My PC is 64 bit, quad core, 5gb of RAM etc
  • I am using the latest version of Power BI Desktop - uninstalled and freshly reinstalled two days ago
  • Connecting to the same XML using the same connection strings in Excel Power Query loads the data in seconds - however, I am not applying the majority of steps that exist in Power BI

I have another report which connects to 11 Excel files on my PC, each file having between 3 and 65 rows - this can also take minutes to update. I have watched 14 rows at the "Load data to model" stage for 5 minutes - this can't be right, can it?

 

While waiting for the refresh to finish, my PC becomes totally unresponsive - Task Manager shows maybe 35% CPU and 75% RAM usage and the old "Mashup" container is there 3 or more times while this is going on.

 

I accept there must be some room for me to improve my structure, but there does seem to be something fundamentally wrong when 14 rows need 5 minutes to load.

 

Any help would be appreciated. 

Regular Visitor
Posts: 30
Registered: ‎07-05-2017

Re: Loading Data to Model Takes Ages

I can help if you could share the "first few lines" of your query from the advanced editor so that we can see how you connect to the data.

 

Member
Posts: 74
Registered: ‎07-27-2017

Re: Loading Data to Model Takes Ages

This is the initial connection (with changes to certain values for security purposes):

 

let
Source = Xml.Tables(Web.Contents("https://app.this_is_fake.com/report/code/Budget%20vs%20Actuals?format=xml&UserName=this_is_fake&Acco...")),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ReportName", type text}, {"ReportType", type text}, {"ParameterSummary", type text}, {"TimeProcessed", type datetime}, {"Uid", type number}}),
Rows = #"Changed Type"{0}[Rows],
Row = Rows{0}[Row],
#"Changed Type1" = Table.TransformColumnTypes(Row,{{"ClientName", type text}, {"EngagementCode", type text}, {"EngagementName", type text}, {"EngagementType", type text}, {"EngagementRegion", type text}, {"ProjectCode", type text}, {"ProjectName", type text}, {"ProjectStage", type text}, {"ProjectManager", type text}, {"ProjectStartDate", type datetime}, {"EngagementUID", type text}, {"ProjectUID", type text}, {"Client", type text}, {"Engagement", type text}, {"BudgetedBillingAdjustedRevenueatCompletion", type number}, {"ActualBillingAdjustedRevenue", type number}, {"BudgetedBillingAdjustedRevenueRemaining", type number}, {"BudgetedChargeableHoursatCompletion", type number}, {"ActualHourstoDate", type number}, {"ActualChargeableHourstoDate", type number}, {"BudgetedChargeableHoursRemaining", type number}, {"ProjectEndDate", type datetime}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"ProjectStartDate", type date}, {"ProjectEndDate", type date}})
in
#"Changed Type2"

 

Then the query which references this and does all the transforming:

 

let
Source = #"BvA Projects",
#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([EngagementName], "DELETE") and not Text.Contains([EngagementName], "DO NOT USE") and not Text.Contains([EngagementCode], "DELETE") and not Text.Contains([EngagementCode], "DO NOT USE") and not Text.Contains([ProjectName], "DELETE") and not Text.Contains([ProjectName], "DO NOT USE") and not Text.Contains([ProjectCode], "DELETE") and not Text.Contains([ProjectCode], "DO NOT USE")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,0,Replacer.ReplaceValue,{"BudgetedBillingAdjustedRevenueatCompletion", "BudgetedBillingAdjustedRevenueRemaining"}),
#"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "nBudget Value", each if [BudgetedBillingAdjustedRevenueatCompletion] >= 1000000000 then "0" else if [BudgetedBillingAdjustedRevenueatCompletion] = null then "0" else [BudgetedBillingAdjustedRevenueatCompletion] ),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "n.Budget Remaining", each if [BudgetedBillingAdjustedRevenueRemaining] >= 1000000000 then null else if [BudgetedBillingAdjustedRevenueatCompletion] = null then null else [BudgetedBillingAdjustedRevenueRemaining] ),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"nBudget Value", type number}, {"n.Budget Remaining", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"ActualBillingAdjustedRevenue", "Revenue"}, {"n.Budget Remaining", "Budget Remaining"}, {"ClientName", "Client Name"}, {"EngagementCode", "Code"}, {"EngagementName", "Engagement Name"}, {"EngagementType", "Engagement Type"}, {"ProjectCode", "Project Code"}, {"ProjectName", "Project Name"}, {"ProjectStage", "Project Stage"}, {"ProjectManager", "Project Manager"}, {"ProjectStartDate", "Project Start Date"}, {"nBudget Value", "Budget Value"}}),
#"Replaced Value1" = Table.ReplaceValue(#"Renamed Columns"," ()","",Replacer.ReplaceText,{"Project Manager"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1"," (1)","",Replacer.ReplaceText,{"Project Manager"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2",null,0,Replacer.ReplaceValue,{"Budget Remaining"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value3", "Potential Revenue", each if [Budget Remaining] < 0 then [Budget Remaining] * -1 else 0),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom",{{"Potential Revenue", type number}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type3", "Budgeted Revenue", each if [Revenue] < [Budget Value] then [Revenue] else [Budget Value]),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom1",{{"Budgeted Revenue", type number}}),
#"Added Conditional Column2" = Table.AddColumn(#"Changed Type4", "Available Budget", each if [Budget Remaining] >= 0 then [Budget Remaining] else "0" ),
#"Changed Type5" = Table.TransformColumnTypes(#"Added Conditional Column2",{{"Available Budget", type number}}),
#"Added Conditional Column3" = Table.AddColumn(#"Changed Type5", "Overspend", each if [Budget Remaining] <= 0 then [Budget Remaining] else "0" ),
#"Changed Type6" = Table.TransformColumnTypes(#"Added Conditional Column3",{{"Overspend", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type6",{"ProjectUID"},#"Tasks by Client",{"ProjectUID"},"Tasks by Client",JoinKind.LeftOuter),
#"Aggregated Tasks by Client" = Table.AggregateTableColumn(#"Merged Queries", "Tasks by Client", {{"Resource Cost", List.Sum, "Sum of Resource Cost"}}),
#"Changed Type7" = Table.TransformColumnTypes(#"Aggregated Tasks by Client",{{"Sum of Resource Cost", type number}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type7",{{"Sum of Resource Cost", "Resource Cost"}}),
#"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "TempEndDate", each if [ProjectEndDate] = null then Date.From(DateTime.LocalNow()) else [ProjectEndDate]),
#"Add FM No" = Table.AddColumn(#"Added Custom2", "FM Number", each if Date.Month([#"TempEndDate"]) = 9 then 12 else Number.Mod(Date.Month([#"TempEndDate"])+3, 12)),
#"Add FQ No" = Table.AddColumn(#"Add FM No", "FQ Number", each Number.Mod(Date.QuarterOfYear([#"TempEndDate"]),4)+1),
#"Add FY Display" = Table.AddColumn(#"Add FQ No", "Year (Financial)", each if [#"FQ Number"] = 1 then Date.Year([#"TempEndDate"]) + 1 else Date.Year([#"TempEndDate"])),
#"Add FM Display" = Table.AddColumn(#"Add FY Display", "Month (Financial)", each Text.From([#"Year (Financial)"]) & "/" & Text.PadStart(Text.From([FM Number]), 2, "0") & " - " & Date.MonthName([#"TempEndDate"])),
#"Add FQ Display" = Table.AddColumn(#"Add FM Display", "Quarter (Financial)", each Text.From([#"Year (Financial)"]) & " - Q" & Text.From([FQ Number])),
#"Removed Other Columns" = Table.SelectColumns(#"Add FQ Display",{"Client Name", "Code", "Engagement Name", "Engagement Type", "EngagementRegion", "Project Code", "Project Name", "Project Stage", "Project Manager", "Project Start Date", "ProjectEndDate", "EngagementUID", "ProjectUID", "Client", "Engagement", "BudgetedBillingAdjustedRevenueatCompletion", "Revenue", "BudgetedBillingAdjustedRevenueRemaining", "BudgetedChargeableHoursatCompletion", "ActualHourstoDate", "ActualChargeableHourstoDate", "BudgetedChargeableHoursRemaining", "Budget Value", "Budget Remaining", "Potential Revenue", "Budgeted Revenue", "Available Budget", "Overspend", "Resource Cost", "Year (Financial)"}),
#"Added Conditional Column4" = Table.AddColumn(#"Removed Other Columns", "isDesiredFY", each if [#"Year (Financial)"] >= #"Earliest FY Projects" then "true" else "false" ),
#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column4",{{"isDesiredFY", type logical}}),
#"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([isDesiredFY] = true))
in
#"Filtered Rows2"

 

I appreciate that there is some clunky stuff going on with some of the calculated columns... I'm not capable of replacing them with functions yet (or even sure that this is the problem)

Regular Visitor
Posts: 30
Registered: ‎07-05-2017

Re: Loading Data to Model Takes Ages

Assuming that your Web Source's latency is not the problem here; my hunch is the Table.NestedJoin is the problematic part here.

You can try removing your steps including and after the merge you do, and try to Apply and load the data model. If it will work fast that you got your diagnostic confirmed.

 

Once confirmed, you can write us and maybe an the tech expert for M can explain why and suggest a better way.

Member
Posts: 74
Registered: ‎07-27-2017

Re: Loading Data to Model Takes Ages

I am comfortable that the web source latency is not an issue - I have an Excel spreadsheet connecting to all of the same connections in order to get a view of the raw data while testing, and this loads in seconds.

 

I will follow your suggestion of systematically reducing the steps to see how this affects loading time and will report back.

 

Thanks.