Data loading in power query but not in power BI

[vasu1102]

Hi,

I have 2 csv data sources and 5-6 SQL tables. I am performing some transformations on this data in power query and the end-result loads in power query in a matter of seconds. After that when I try to return to power bi the data load gets stuck and just continues for hours on end. I have tried checking/unchecking different options in power bi settings, tried deleting cache, temp files, reinstalling power bi etc. Nothing has worked. Is there anything one can do to systematically find the root cause of the issue? Power BI is not giving any information as to what it is doing while stuck.

Br

Pankaj

[vasu1102]

I did not get the answer I was expecting but it was helpful getting the message that it was not an optimal solution which I was trying to implement. So, I have changed the whole logic considerably and made it much lighter.

[vasu1102]

Hi @Daryl-Lynch-Bzy - I am merging the CSV and SQL data in Power Query. The memory usage is quite high when Power BI gets stuck so it does look like your hypothesis about the memory becoming a bottleneck might be right.

But, it doesn't look like it is because of any errors in power query as the table it gets stuck loading has not errors at the end of the needed transformations in Power query.

Is there a workaround to this issue? I need to get some data into the model which is not in SQL and it has to be in a way which relatively straightforward (like a csv file).

Here's the power query code of the table which power bi gets stuck downloading:

let
Source = Forecast,
#"Removed Other Columns" = Table.SelectColumns(Source,{"End user country_Text", "Material", "PPN", "Launch date", "Adjusted GSF selected version(U3)", "Adjusted FiscalDate"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Other Columns", {"End user country_Text", "Material", "Adjusted FiscalDate"}, PackStockAtAffiliate, {"CountryText", "Matno", "StockAvailabilityMonth"}, "PackStockAtAffiliate", JoinKind.LeftOuter),
#"Expanded PackStockAtAffiliate" = Table.ExpandTableColumn(#"Merged Queries", "PackStockAtAffiliate", {"SummedQuantityU3"}, {"StockQuantityU3"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded PackStockAtAffiliate",null,0,Replacer.ReplaceValue,{"StockQuantityU3"}),
#"Merged Queries1" = Table.NestedJoin(#"Replaced Value", {"End user country_Text", "Material", "Adjusted FiscalDate"}, OrdersData, {"CountryText", "Matno", "RoundedDeliveryDate"}, "OrdersData", JoinKind.LeftOuter),
#"Expanded OrdersData" = Table.ExpandTableColumn(#"Merged Queries1", "OrdersData", {"SummedOrderQuantityU3"}, {"OrderQuantityU3"}),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded OrdersData",null,0,Replacer.ReplaceValue,{"OrderQuantityU3"}),
#"Merged Queries2" = Table.NestedJoin(#"Replaced Value1", {"End user country_Text"}, LaunchOrderParameter, {"Country"}, "LaunchOrderParameter", JoinKind.LeftOuter),
#"Expanded LaunchOrderParameter" = Table.ExpandTableColumn(#"Merged Queries2", "LaunchOrderParameter", {"Size (days of forecast)", "FP inventory policy (DoH)", "Future period for averaging demand"}, {"Size (days of forecast)", "FP inventory policy (DoH)", "Future period for averaging demand"}),
#"Inserted Merged Column" = Table.AddColumn(#"Expanded LaunchOrderParameter", "End user country_Text - Material", each Text.Combine({[End user country_Text], [Material]}, " - "), type text),
#"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"End user country_Text", "Material"}, {{"ForecastEndDate", each List.Max([Adjusted FiscalDate]), type date}, {"ForecastData", each _, type table [End user country_Text=text, Material=text, PPN=nullable text, Launch date=nullable date, #"Adjusted GSF selected version(U3)"=number, Adjusted FiscalDate=date, StockQuantityU3=nullable number, OrderQuantityU3=nullable number, #"Size (days of forecast)"=any, #"FP inventory policy (DoH)"=nullable text, Future period for averaging demand=nullable text, #"End user country_Text - Material"=text]}}),
#"Expanded ForecastData" = Table.ExpandTableColumn(#"Grouped Rows", "ForecastData", {"PPN", "Launch date", "Adjusted GSF selected version(U3)", "Adjusted FiscalDate", "StockQuantityU3", "OrderQuantityU3", "Size (days of forecast)", "FP inventory policy (DoH)", "Future period for averaging demand", "End user country_Text - Material"}, {"PPN", "Launch date", "Adjusted GSF selected version(U3)", "Adjusted FiscalDate", "StockQuantityU3", "OrderQuantityU3", "Size (days of forecast)", "FP inventory policy (DoH)", "Future period for averaging demand", "End user country_Text - Material"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded ForecastData",{{"FP inventory policy (DoH)", Int64.Type}, {"Future period for averaging demand", Int64.Type}, {"Size (days of forecast)", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"End user country_Text - Material", Order.Ascending}, {"Adjusted FiscalDate", Order.Ascending}}),
Table.AppendColumn = (table, columnName, values) => Table.TransformColumns(Table.AddIndexColumn(table, columnName), {columnName, (i) => values{i}}),
Table.AppendRunningSum = (table, newColumn) => Table.AppendColumn(table, newColumn, fxFPLaunchOrders(List.Buffer(table[#"Adjusted GSF selected version(U3)"]), List.Buffer(table[#"Size (days of forecast)"]), List.Buffer(table[#"FP inventory policy (DoH)"]), List.Buffer(table[#"Launch date"]), List.Buffer(table[#"Adjusted FiscalDate"]), List.Buffer(table[#"End user country_Text - Material"]), List.Buffer(table[#"ForecastEndDate"]), List.Buffer(table[#"Future period for averaging demand"]), List.Buffer(table[#"StockQuantityU3"]), List.Buffer(table[#"OrderQuantityU3"]))),
WithOrders = Table.AppendRunningSum(#"Sorted Rows", "FPOrders")
in
WithOrders

Br

Pankaj

[Daryl-Lynch-Bzy]

Wow!! There is a lot to digest and there are things that I don't recommend.  For example, this step

Table.AppendRunningSum

Contains several List.Buffers that will eat memory.

It is difficult to advise on this forum without understand the data sources.  You would need to upload a sample file with objects named as CSV or SQL where appropriate.

[AlexisOlson]

Yeah, lots of complexity here. The function definition for Table.AppendColumn looks like it's probably not going to be calculated efficiently (extracting elements via index probably isn't optimized) and fxFPLaunchOrders isn't defined where we can see.

[vasu1102]

Thank you both. I am a little bit hesitant sharing details of the function and the data here because this is work which I am doing for my company and I am unsure of how much I can share in a public forum.

I don't have a lot of experience programming in M or DAX so this was my first shot at trying to build a logic which works but I just wasn't expecting that I would run into such performance issues with such a small dataset (approx. 20,000 rows) and a power bi file which is about 1.2 MB. My intent with reaching out was to find out whether there was a way of systematically finding out where the performance bottlenecks are so that I can then focus my time and energy on the specific routines. I guess you guys know what you know from experience but for a beginner like me I am quite lost when Power BI or Power Query just hang and there is no clear way of troubleshooting the problem.

[Daryl-Lynch-Bzy · Accepted solution]

Hi @vasu1102 - are you merging the CSV data and the SQL data in Power Query?  If so, this can lead to loads of performance issue because Power BI may not have the memory,  We would need more details to help further.