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

 

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)

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

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

  • hohlick's avatar
    hohlick
    Continued Contributor
    10 years ago

    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's avatar
      ImkeF
      Community Champion
      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)
      • hohlick's avatar
        hohlick
        Continued Contributor
        10 years ago

        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