Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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)
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
Out of curiousity, how many rows in your Excel file and how long is the process taking?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |