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
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
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
FilteredTableit'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