Forum Discussion

MarkCBB's avatar
MarkCBB
Helper V
10 years ago
Solved

Faster M needed

  Hello,   I am using the following Query in PBI Desktop, however it is really really slow:   let Source = Excel.Workbook(File.Contents(File_Dir & "\eXceler8\Projects\Excel Add-Ins\Clients\...
Need Help
  • ImkeF's avatar
    ImkeF
    10 years ago

    Hi Maxim :-)

    Are you sure that this will prevent the multiple calls to the SALES-table?

    To be on the safe side here it would look like this:

     

    SalesDate = List.Buffer(SALES[DATE]),
LMinSD = List.Min(SalesDate),
LMaxSD = List.Max(SalesDate),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= LMinSD and [DATE] <= LMaxSD)
ImkeF's avatar
ImkeF
Community Champion
10 years ago

Well, this was more about buffering than about shortening :-)

 

Although your code reads as if the (usually long) Sales-table is only adressed twice, I've seen cases where - when referenced in functions - they would be called multiple times. This will be prevented by the List.Buffer.

hohlick's avatar
hohlick
Continued Contributor
10 years ago

Agreed. I do not use buffer often, because my data source is small enough, so I almost never meet problems, but it is really useful trick.

Actually, M is tricky enough - you can find a lot of ways to do same things :)

  • MarkCBB's avatar
    MarkCBB
    Helper V
    10 years ago

    Greg_Deckler There are 231 Excel files that are used in the sales database, (Grows by 1 per day - Going to fix this as it is per date and some of the files can be combined - :Edited: see below, implemented). Current Rows in the sales table: 4 million - not super big.

     

     

    The following query takes: +/-3 Mins ImkeF Rock star!!!

    let

    SalesDate = List.Buffer(SALES[DATE]),
    LMinSD = List.Min(SalesDate),
    LMaxSD = List.Max(SalesDate),

    Source = Excel.Workbook(File.Contents(File_Dir & "\eXceler8\Projects\Excel Add-Ins\Clients\iRam Internal\Live files\iRAM_ADDIN_APP_01\SSF\SF\SFS\1PBI_DB\Support Tables\Calendar.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month Year", type text}, {"Quarter Year", type text}, {"Week Year", type text}, {"Quarter", type text}, {"Month", type text}, {"Week of Year", type text}, {"Day of Week", type text}, {"Date", type date}, {"Day of Month", Int64.Type}, {"Day of Week ID", Int64.Type}, {"Week of Year ID", Int64.Type}, {"Month_ID", Int64.Type}, {"Quarter ID", Int64.Type}, {"Year", Int64.Type}, {"Week Year ID", Int64.Type}, {"Month Year ID", Int64.Type}, {"Quarter Year ID", Int64.Type}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "DATE"}}),

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= LMinSD and [DATE] <= LMaxSD)

in
    #"Filtered Rows"

     

     

    The query as I had it before takes: +/- 13 Mins

    let

    Source = Excel.Workbook(File.Contents(File_Dir & "\eXceler8\Projects\Excel Add-Ins\Clients\iRam Internal\Live files\iRAM_ADDIN_APP_01\SSF\SF\SFS\1PBI_DB\Support Tables\Calendar.xlsx"), null, true),

    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],

    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Month Year", type text}, {"Quarter Year", type text}, {"Week Year", type text}, {"Quarter", type text}, {"Month", type text}, {"Week of Year", type text}, {"Day of Week", type text}, {"Date", type date}, {"Day of Month", Int64.Type}, {"Day of Week ID", Int64.Type}, {"Week of Year ID", Int64.Type}, {"Month_ID", Int64.Type}, {"Quarter ID", Int64.Type}, {"Year", Int64.Type}, {"Week Year ID", Int64.Type}, {"Month Year ID", Int64.Type}, {"Quarter Year ID", Int64.Type}}),

    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Date", "DATE"}}),

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DATE] >= List.Min(SALES[DATE]) and [DATE] <= List.Max(SALES[DATE]))

in
    #"Filtered Rows"

    Thank you once again. 

     

     

    • jtkr996's avatar
      jtkr996
      New Member
      9 years ago

      Hello,

       

      I'm also trying to use the List.Buffer function for a very similar task and borrowed heavily from this example.

       

      I have dates in my source data up to 4/30/2017 however the List.Max is returning 12/31/2016 rather than the latest date.

       

      My souce is a .csv file so I guess it's treating them as text and not sorting them numerically?  Also noteworth is that when the list is sorted decending 12/31/16 appears as the latest despite later dates being present in the list.

       

      The dates are in column 32 of my .csv which "Last Sold Date" is the header/first row. Without removing the title it was being returned with the List.Max function instead of the 12/31/16 so I removed it.

       

       

      let
   
    LastSold = List.Buffer(#"Imported CSV"[Column32]),
    #"Removed Items" = List.RemoveMatchingItems(LastSold,{"Last Sold Date"}),
    LsMax = List.Max(LastSold),

      Any help would be appreciated.

       

      • hohlick's avatar
        hohlick
        Continued Contributor
        9 years ago
        HI jtkr996
        Yes, your dates recognized as a text.
        You can transform them to the dates by several techniques:
        1. Use Date.From function, where the second argument is "en-US"
        2. Apply "Change type with locale" using UI - right click on the column, change with locale, then select date type and US locale (or via type assign button, to the left of the column name)
        It should works.

        BTW, you can promote first row to the column names instead of filtering it out