Forum Discussion
Power Query Doesn't Load my 6GB / 200K Rows into Excel's View, Even With Data Loading in Power Query
Hello guys, how you all doing,
I developed an M code to get the price value based on some conditions, and it seems to work in Power Query but it takes forever to load in Excel's table view (and tbqh don't even load at all, I tried to let him cook for 1 whole day).
Here's my M code:
let
Source = Table.Combine({PRICES_A, PRICES_B, PRICES_C, PRICES_D, PRICES_E, PRICES_F, PRICES_G, PRICES_H, PRICES_I, PRICES_J, PRICES_K, PRICES_L, PRICES_M, PRICES_N, PRICES_O, PRICES_P}),
#"Dates" = List.Buffer(List.Sort(Source[DATE])),
#"StepBack" = Source,
#"Ad_Previous" = Table.AddColumn(#"StepBack", "DATE D-1", each try Dates{List.PositionOf(#"Dates", [DATE])-1} otherwise null, type date),
#"Added Custom" = Table.AddColumn(#"Ad_Previous", "PRICE D-1", each [d1 = [DATE D-1], columnCONDITION1 = [columnCONDITION1], columnCONDITION2 = [columnCONDITION2], columnCONDITION3 = [columnCONDITION3], res = List.Sum(Table.SelectRows(#"Ad_Previous", each [DATE] = d1 and [columnCONDITION1] = columnCONDITION1 and [columnCONDITION2] = columnCONDITION2 and [columnCONDITION3] = columnCONDITION3) [PRICE])] [res])
in
#"Added Custom"
So, the idea is: I have a column named "PRICE" and I need to create a column named "PRICE D-1", with the previous price available with the same conditions from each row from "PRICE" column.
So for example, let's say that I have a price available in 10/10/2025 with the conditions A, B and C for the columns "columnCONDITION1", "columnCONDITION2" and "columnCONDITION3". Now I need to collect the previous price available (based on the "DATE D-1" column), which I named as "PRICE D-1", with the same conditions "columnCONDITION1", "columnCONDITION2" and "columnCONDITION3".
That said, my M code works just fine and it takes like 15min to load that logic into Power Query (around 6GB of data and 200K rows). But when I "Close and Load", just takes forever to load, and never loads into Excel's view.
I tried a couple of things to manage that issue, including Buffering (List.Buffer and Table.Buffer), but it seems to not work for me. Maybe something in my code slows it down, even with Buffer, like my List.Sum or something else, I just don't know what.
Funfact is that my M code loads into Excel's view up to #"Ad_Previous" step, so the problem really happends into my "PRICE D-1" code.
Any suggestions to speed up that M code for a better result and actually loading my data?
Thanks in advance.
bubbledep Let's try with:
let
Source = Table.Combine({PRICES_A, PRICES_B, PRICES_C, PRICES_D, PRICES_E, PRICES_F, PRICES_G, PRICES_H, PRICES_I, PRICES_J, PRICES_K, PRICES_L, PRICES_M, PRICES_N, PRICES_O, PRICES_P}),
// Sort by DATE in ascending order
SortedTable = Table.Sort(Source, {{"DATE", Order.Ascending}}),// Group by all condition columns to ensure we find previous prices within the same group
GroupedTable = Table.Group(SortedTable, {"columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
{{"All Data", each _, type table [DATE=nullable date, PRICE=nullable number]}}),// Add a column to find previous DATE and PRICE
AddPreviousPrice = Table.AddColumn(GroupedTable, "All Data", each
let
Grouped = [All Data],
Sorted = Table.Sort(Grouped, {{"DATE", Order.Ascending}}),
AddPrevious = Table.AddColumn(Sorted, "Prev Date", each try Sorted[DATE]{List.PositionOf(Sorted[DATE], [DATE])-1} otherwise null),
AddPrevPrice = Table.AddColumn(AddPrevious, "Prev Price", each try Sorted[PRICE]{List.PositionOf(Sorted[DATE], [DATE])-1} otherwise null)
in
AddPrevPrice, type table [DATE=nullable date, PRICE=nullable number, Prev Date=nullable date, Prev Price=nullable number]
),// Expand back the grouped data
ExpandedTable = Table.ExpandTableColumn(AddPreviousPrice, "All Data", {"DATE", "PRICE", "Prev Date", "Prev Price"}),// Rename columns for clarity
RenamedColumns = Table.RenameColumns(ExpandedTable, {{"Prev Date", "DATE D-1"}, {"Prev Price", "PRICE D-1"}})
in
RenamedColumnsBBF
15 Replies
bubbledep Hi! Instead of filtering rows dynamically in Table.SelectRows, you can merge the table with itself based on the previous date and conditions.
Something like this:
let
// Combine all price tables into one
Source = Table.Combine({PRICES_A, PRICES_B, PRICES_C, PRICES_D, PRICES_E, PRICES_F, PRICES_G, PRICES_H, PRICES_I, PRICES_J, PRICES_K, PRICES_L, PRICES_M, PRICES_N, PRICES_O, PRICES_P}),
// Sort the table by DATE in ascending order
SortedTable = Table.Sort(Source, {{"DATE", Order.Ascending}}),// Create a reference table with DATE shifted by -1
PreviousDates = Table.SelectColumns(SortedTable, {"DATE", "columnCONDITION1", "columnCONDITION2", "columnCONDITION3", "PRICE"}),
RenamedPrevious = Table.RenameColumns(PreviousDates, {{"DATE", "DATE D-1"}, {"PRICE", "PRICE D-1"}}),// Merge the original table with the shifted table on DATE D-1 and conditions
MergedTable = Table.NestedJoin(
SortedTable,
{"DATE", "columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
RenamedPrevious,
{"DATE D-1", "columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
"PreviousPrice",
JoinKind.LeftOuter
),// Expand the merged column to get the previous price
FinalTable = Table.ExpandTableColumn(MergedTable, "PreviousPrice", {"PRICE D-1"})
in
FinalTableBBF
Hello BeaBF , how you doing,
Thanks for the answer, that really helped me to improve the load time. But unfortunately what I need is that in the same row I have the current date with the current price and also the previous date with the previous price, like I highlighted below.
It's possible to achieve that result by adjusting your code above?
Thanks!
bubbledep Yes! You need to slightly modify the approach so that each row contains both the current price and the previous price from the closest available date while ensuring that all conditions match.
Try with:
let
// Combine all price tables into one
Source = Table.Combine({PRICES_A, PRICES_B, PRICES_C, PRICES_D, PRICES_E, PRICES_F, PRICES_G, PRICES_H, PRICES_I, PRICES_J, PRICES_K, PRICES_L, PRICES_M, PRICES_N, PRICES_O, PRICES_P}),
// Sort by DATE ascending
SortedTable = Table.Sort(Source, {{"DATE", Order.Ascending}}),// Create a reference table with DATE shifted by -1
PreviousPrices = Table.SelectColumns(SortedTable, {"DATE", "columnCONDITION1", "columnCONDITION2", "columnCONDITION3", "PRICE"}),
// Rename columns to avoid conflicts
RenamedPrevious = Table.RenameColumns(PreviousPrices, {{"DATE", "DATE D-1"}, {"PRICE", "PRICE D-1"}}),// Merge the original table with the shifted table on DATE D-1 and conditions
MergedTable = Table.NestedJoin(
SortedTable,
{"columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
RenamedPrevious,
{"columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
"PreviousPrice",
JoinKind.LeftOuter
),// Expand the merged column to get DATE D-1 and PRICE D-1
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousPrice", {"DATE D-1", "PRICE D-1"}),// Filter to ensure DATE D-1 is actually before DATE
FilteredTable = Table.SelectRows(ExpandedTable, each [DATE D-1] <> null and [DATE D-1] < [DATE])
in
FilteredTableBBF
But why do you refer to the Source in #"Step ack" when you could just refer to the actual Source in the subsequent step? Since Stepback has to be evaluated by the next step, that means that you have to run that gigantic Source step twice. Also, it's difficult to understand what you could be doing with your "Dates{List.PositionOf..." whole thing. This appears to be an unusually unwieldy and inefficient query. You, gotta simplify, maybe break this thing up and rethink this whole thing.
--Nate
Due to the loading mechanism of Power Query, only the first 1,000 rows of results will be loaded during preview. However, in actual calculations, Power Query may load the same file multiple times.
For this slightly complex calculation, if you are familiar with DAX, the best practice is to use DAX. The specific steps are:
- Use Power Query to combine tables
- Load into Power Pivot
- Use DAX Studio to create a custom query
- Load into Excel (in a dynamic connection way)
Based on past experience, for 200,000 rows of data, after Power Query loaded the data, DAX calculations and table style updates will not exceed 1 minute.
