Forum Discussion
Power Query Doesn't Load my 6GB / 200K Rows into Excel's View, Even With Data Loading in Power Query
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
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?
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
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!
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
FilteredTableBBF
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?