Table.buffer slow

[Anonymous]

Hi experts,

i was wondering if anyone know how to speed this up? I'm importing an Excel workbook with 17K records and it takes around 30minutes to process this. Importing the data without the buffer code takes one minute, so i'm trying to find a way to speed it up.

What is it that i'm doing:

The code checks sums the value in Q_label_score_maand by Equipment series, Workcenter and Serial number over the last 3 months. So if it's april, it should show the sum of the values from january till march.

let
    Bron = Excel.Workbook(File.Contents("C:\Users\" & user & "\OneDrive - NS\Borgspoor\RAAK\Data bestanden\RAAK - storingen per maand.xlsx"), null, true),
    Dataset_Sheet = Bron{[Item="Dataset",Kind="Sheet"]}[Data],
    #"Headers met verhoogd niveau" = Table.PromoteHeaders(Dataset_Sheet, [PromoteAllScalars=true]),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Headers met verhoogd niveau",{{"Serial Number", Int64.Type}, {"Jaar maand", type text}, {"Equipment Series", type text}, {"Work Center", type text}, {"SR Number", Int64.Type}, {"SR Stranded Train Number", Int64.Type}, {"SR Safety Number", Int64.Type}, {"EBK Number", type any}, {"Recidivist", Int64.Type}, {"Q_label_score_maand", Int64.Type}, {"Qlabelscore_laatste_3_maanden", type any}, {"Qlabel", type text}}),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"Type gewijzigd",{"Qlabelscore_laatste_3_maanden"}),
    #"Inserted Date" = Table.AddColumn(#"Kolommen verwijderd", "Datum", each Date.From([Jaar maand]), type date),
    #"Adds 3 months back" = Table.AddColumn(#"Inserted Date", "Date", each Date.AddMonths([Datum],-3), type date),
    BufferValues = Table.Buffer(#"Adds 3 months back")
    ,#"Adds last 3 months" = Table.AddColumn(#"Adds 3 months back", "Q-Score 3mnd", each List.Sum(
Table.SelectRows(BufferValues, 
let 
snr = [Serial Number] , 
eq = [Equipment Series],
wc = [Work Center],
sdate = [Date] ,
edate = [Datum]
in 
each 
[Serial Number] = snr and    
[Work Center] = wc and
[Equipment Series] = eq and
[Datum] >= sdate and
[Datum] < edate 

)[Q_label_score_maand]), Int64.Type),
    #"Qlabel 3mnd" = Table.AddColumn(#"Adds last 3 months", "Q_lable_3mnd", each if [#"Q-Score 3mnd"] <= 9 then "A" else if [#"Q-Score 3mnd"] <= 12 then "B" else if [#"Q-Score 3mnd"] <= 14 then "C" else if [#"Q-Score 3mnd"] <= 17 then "D" else if [#"Q-Score 3mnd"] <= 22 then "E" else if [#"Q-Score 3mnd"] <= 30 then "F" else if [#"Q-Score 3mnd"] >= 31 then "G" else null),
    #"Qlabel 3mnd Fouten vervangen" = Table.ReplaceErrorValues(#"Qlabel 3mnd", {{"Q_lable_3mnd", null}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Qlabel 3mnd Fouten vervangen", "Vandaaag", each DateTime.LocalNow())
in
    #"Aangepaste kolom toegevoegd" 

[v-kkf-msft]

Hi @Anonymous ,

If the following code is used without the Table.Buffer function, how long does it take? If it takes longer, then I think using DAX instead of M code would be a better choice.

let
    Source = Excel.Workbook(File.Contents("\.....\CASE SAMPLE DATA\RAAK - storingen per maand.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Serial Number", Int64.Type}, {"Jaar maand", type date}, {"Equipment Series", type text}, {"Work Center", type text}, {"SR Number", Int64.Type}, {"SR Stranded Train Number", Int64.Type}, {"SR Safety Number", Int64.Type}, {"EBK Number", Int64.Type}, {"Recidivist", Int64.Type}, {"Q_label_score_maand", Int64.Type}, {"Qlabelscore_laatste_3_maanden", Int64.Type}, {"Qlabel", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Qlabelscore_laatste_3_maanden"}),
    #"Inserted Date" = Table.AddColumn(#"Removed Columns", "Datum", each Date.From([Jaar maand]), type date),
    #"Adds 3 months back" = Table.AddColumn(#"Inserted Date", "Date", each Date.AddMonths([Datum],-3), type date),
    #"Adds last 3 months" = Table.AddColumn(#"Adds 3 months back", "Q-Score 3mnd", each let 
    myfunction = (CurrentSerial, CurrentEquipment, CurrentWorkCenter,sdate, edate) => 
     let 
       SelectRows = Table.SelectRows(#"Adds 3 months back", each [Serial Number] = CurrentSerial and [Equipment Series] = CurrentEquipment and [Work Center] = CurrentWorkCenter and [Datum] >= sdate and [Datum] < edate) 
     in 
       SelectRows,
   Data = List.Sum(myfunction([Serial Number],[Equipment Series],[Work Center],[Date],[Datum])[Q_label_score_maand])
in 
   Data),
    #"Qlabel 3mnd" = Table.AddColumn(#"Adds last 3 months", "Q_lable_3mnd", each if [#"Q-Score 3mnd"] <= 9 then "A" else if [#"Q-Score 3mnd"] <= 12 then "B" else if [#"Q-Score 3mnd"] <= 14 then "C" else if [#"Q-Score 3mnd"] <= 17 then "D" else if [#"Q-Score 3mnd"] <= 22 then "E" else if [#"Q-Score 3mnd"] <= 30 then "F" else if [#"Q-Score 3mnd"] >= 31 then "G" else null),
    #"Qlabel 3mnd Fouten vervangen" = Table.ReplaceErrorValues(#"Qlabel 3mnd", {{"Q_lable_3mnd", null}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Qlabel 3mnd Fouten vervangen", "Vandaaag", each DateTime.LocalNow())
in
    #"Aangepaste kolom toegevoegd"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-kkf-msft , tnx for the help, but it was actually slower then the original code. I aborted the refresh after 42 minutes. So maybe writing it in DAX is the better solution. I'll try to write it and post it here