Forum Discussion

bubbledep's avatar
bubbledep
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's avatar
    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's avatar
BeaBF
Super User
1 year ago

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's avatar
    bubbledep
    Helper I
    1 year ago

    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's avatar
      BeaBF
      Super User
      1 year ago

      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's avatar
        bubbledep
        Helper I
        1 year ago

        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?