Faster M needed

[MarkCBB]

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

This is the Line that i Taking so long to load:

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

Is there any other way I can accomplish the same thing but alot faster?

[hohlick]

try this 

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

it seems that you on each step (each row) calculate min and max again and again 

[ImkeF · Accepted solution]

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)

[hohlick]

Hi Imke!

You shortened it, again 🙂 I think, it will be a little faster. Probably, if TS will turn off "Time intelligence", it could help also

[ImkeF]

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]

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]

@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]

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]

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

[Greg_Deckler]

Out of curiousity, how many rows in your Excel file and how long is the process taking?