Power Query Doesn't Load my 6GB / 200K Rows into Excel's View, Even With Data Loading in Power Query

[bubbledep]

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.

[ZhangKun]

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.

[nathancwatkins]

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

[BeaBF]

@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
FinalTable

BBF

[bubbledep]

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!

[BeaBF]

@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
FilteredTable

BBF

[bubbledep]

It seems that makes my table empty, see below.

Maybe it's something with the conditions? Basically, I need the conditions to be the same fields from each column. So that way, the price from the previous date have the same characteristics from the price in the current date.

I don't know how to handle this. Any suggestions?

[BeaBF]

@bubbledep retry 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 table by DATE in descending order (latest first)
SortedTable = Table.Sort(Source, {{"DATE", Order.Descending}}),

// Add an index to track order for later
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),

// Create a reference table with shifted previous date
PreviousPrices = Table.SelectColumns(IndexedTable, {"Index", "DATE", "columnCONDITION1", "columnCONDITION2", "columnCONDITION3", "PRICE"}),

// Rename columns for merging
RenamedPrevious = Table.RenameColumns(PreviousPrices, {
{"DATE", "DATE D-1"},
{"PRICE", "PRICE D-1"}
}),

// Perform a self-join on matching conditions and closest previous date
MergedTable = Table.NestedJoin(
IndexedTable,
{"columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
RenamedPrevious,
{"columnCONDITION1", "columnCONDITION2", "columnCONDITION3"},
"PreviousPrice",
JoinKind.LeftOuter
),

// Expand the merged table to get DATE D-1 and PRICE D-1
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousPrice", {"DATE D-1", "PRICE D-1"}),

// Ensure we only take the closest previous date
FilteredTable = Table.SelectRows(ExpandedTable, each [DATE D-1] <> null and [DATE D-1] < [DATE])
in
FilteredTable

it's difficult to make changes without data, can you provide me a sample table on which do this? and the expected output on this sample table?

BBF

[bubbledep]

@BeaBF , here below I show some sample from what is expected.

The columns DATE D-1 and PRICE D-1 are based on the row data that has the same column info. Notice that the price d-1 from the row 1 (5,413) are referenced by the price from the date 09/01/2025 from the row 2, instead of the date 09/01/2025 from the row 5 (5,3583), since needs to be a price with the same characteristics.

Here below I share the sample in a table format.

DATE	CP00	CP01	CP02	CP03	CP04	CP05	CP06	PRICE	DATE D-1	PRICE D-1
15/01/2025	BAR	34230979	ADIV	a0c674vf-82c5-456a-8ac5-5740cc58641f	S-1	d828b027-c66d-4f12-9e6c-f1ec0ca06bd1	ABT	5,3987	09/01/2025	5,413
09/01/2025	BAR	34230979	ADIV	a0c674vf-82c5-456a-8ac5-5740cc58641f	S-1	d828b027-c66d-4f12-9e6c-f1ec0ca06bd1	ABT	5,413	08/01/2025	5,413
08/01/2025	BAR	34230979	ADIV	a0c674vf-82c5-456a-8ac5-5740cc58641f	S-1	d828b027-c66d-4f12-9e6c-f1ec0ca06bd1	ABT	5,413	03/01/2025	5,23
09/01/2025	DUQ	79013860	ADIV23	b376f-1996-4905-8c93-ac662c3b4f7e	S-2	2a168639-e6f0-433f-8059-f6d90426c0e2	XYZ	5,3583	05/01/2025	5,407
05/01/2025	DUQ	79013860	ADIV23	b376f-1996-4905-8c93-ac662c3b4f7e	S-2	2a168639-e6f0-433f-8059-f6d90426c0e2	XYZ	5,407	02/01/2025	5,4085
02/01/2025	DUQ	79013860	ADIV23	b376f-1996-4905-8c93-ac662c3b4f7e	S-2	2a168639-e6f0-433f-8059-f6d90426c0e2	XYZ	5,4085	31/12/2025	5,3583

Thanks!

[BeaBF]

@bubbledep 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 table by DATE ascending (earliest first)
SortedTable = Table.Sort(Source, {{"DATE", Order.Ascending}}),

// Create a reference table for merging (shifted previous records)
PreviousPrices = Table.SelectColumns(SortedTable, {"DATE", "CP00", "CP01", "CP02", "CP03", "CP04", "CP05", "CP06", "PRICE"}),

// Rename columns for joining
RenamedPrevious = Table.RenameColumns(PreviousPrices, {{"DATE", "DATE D-1"}, {"PRICE", "PRICE D-1"}}),

// Merge on all conditions to ensure correct previous price selection
MergedTable = Table.NestedJoin(
SortedTable,
{"CP00", "CP01", "CP02", "CP03", "CP04", "CP05", "CP06"},
RenamedPrevious,
{"CP00", "CP01", "CP02", "CP03", "CP04", "CP05", "CP06"},
"PreviousPrice",
JoinKind.LeftOuter
),

// Expand 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
FilteredTable

BBF

[bubbledep]

@BeaBF ,

Not working at all. My data has around 200K rows, and the Power Query are returning more then 9Mi rows, something is off.

Also, it is showing this message below.

Something that I think made the job of checking all columns data for just grabbing the previous price with the same characteristics from the current price for the date in the row, would be that step.

#"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])

But I don't know what to do in this case, since you suggested a whole different code.

The result I'm getting after the load is that below (I hidded the other data columns).

Instead of many days "15/01/2025", should be only 1. Also the DATE-1 column aren't returning the correct previous date (09/01/2025) and the same thing is applied for PRICE-1.

Any suggestions to solve this?

[BeaBF · Accepted solution]

@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
RenamedColumns

BBF

[bubbledep]

@BeaBF ,

Thanks man, that's exactly what I needed.

Cheers

[BeaBF]

@bubbledep I'm really happy that we finally made it! Sorry for the countless attempts, but it was quite difficult without the PBIX.

BBF

[bubbledep]

@BeaBF,

Due to GLPD reasons I couldn't share my whole dataset.

But thanks a lot for all help.

Cheers

[BeaBF]

@bubbledep In case this still not working, can you share the pbix?

BBF