Forum Discussion
Faster M needed
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)
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
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.
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 :)
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.
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.
