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

      • ImkeF's avatar
        ImkeF
        Community Champion
        10 years ago

        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.