cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper V
Helper V

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: 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)

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

View solution in original post

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: Faster M needed

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


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

Re: Faster M needed

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 

Maxim Zelensky
excel-inside.pro
Highlighted
Super User III
Super User III

Re: 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)

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

View solution in original post

Highlighted

Re: Faster M needed

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

Maxim Zelensky
excel-inside.pro
Highlighted
Super User III
Super User III

Re: Faster M needed

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

Highlighted

Re: Faster M needed

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 🙂

Maxim Zelensky
excel-inside.pro
Highlighted
Helper V
Helper V

Re: Faster M needed

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

 

 

Highlighted
New Member

Re: Faster M needed

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.

 

Highlighted

Re: Faster M needed

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
Maxim Zelensky
excel-inside.pro

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors