Forum Discussion

Helper I

1 year ago

Solved

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 t...

Need Help

Show and Tell

Tips and Tricks

BeaBF
1 year ago
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

BeaBF

Super User

1 year ago

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

Helper I

1 year ago

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
Super User
1 year ago
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
  Helper I
  1 year ago
  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
    Super User
    1 year ago
    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

Forum Discussion

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

Related content

Fast Load "export" to Excel

Problem loading an excel file in Sharepoint

need help loading excel file in power bi

Incremental Refresh loading file from Excel

Excel incremental load into lakehouse