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
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?
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