Forum Discussion

JImAyers's avatar
JImAyers
Icon for Helper I rankHelper I
3 years ago
Solved

Recursive Power Query - slightly off

I am close to solving a nagging recursive power query that CNENFRNL and others have helped me with. My source table is below as well as the M code I'm using. I have created an index grouped by Employee ID so the index starts at 1 for each employee and increments from there. The goal is to recursively add Days Used for each employee. However the summation has an upper limit of 5 as shown in the Should Be column. The Should Be column is what the RT column in the M code, well, should be.

let
Source = Csv.Document(File.Contents("C:\AyersDOCS\data1.csv"),[Delimiter=",", Columns=5, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee", Int64.Type}, {"BedDate", type datetime}, {"Days Used", Int64.Type}, {"", type text}, {"Shuld Be", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"BedDate", "Date"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {"Employee"}, {{"Count", each _, type table [Employee=nullable number, Date=nullable datetime, Days Used=nullable number, #""=nullable text, Shuld Be=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn([Count],"Index",1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Index"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Other Columns", "Index", {"Employee", "Date", "Days Used", "", "Shuld Be", "Index"}, {"Index.Employee", "Index.Date", "Index.Days Used", "Index.", "Index.Shuld Be", "Index.Index"}),
#"Running Total" = Table.RemoveColumns(Table.AddColumn(#"Expanded Index", "RT", each List.Accumulate(Table.ToRecords(Table.MinN(#"Expanded Index","Index.Index",[Index.Index])), 1, (s,c)=> List.Min({s+c[Index.Days Used],5}))), "Index.Index")
in
#"Running Total"

EmployeeDateDays UsedShould Be
367463/1/2022 0:0000
367464/1/2022 0:0011
367465/1/2022 0:0001
367466/1/2022 0:0012
367467/1/2022 0:00-11
367468/1/2022 0:0012
367469/1/2022 0:0013
285153/1/2020 0:0000
285154/1/2020 0:0000
285155/1/2020 0:0000
285156/1/2020 0:0000
285157/1/2020 0:0000
285152/1/2021 0:0000
285153/1/2021 0:0000
285154/1/2021 0:0000
285155/1/2021 0:0000
285156/1/2021 0:0000
285157/1/2021 0:0011
285158/1/2021 0:0012
285159/1/2021 0:0013
2851510/1/2021 0:0014
2851511/1/2021 0:0015
2851512/1/2021 0:0015
285151/1/2022 0:0015
285152/1/2022 0:0015
285153/1/2022 0:00-14
285154/1/2022 0:0015
285155/1/2022 0:0015
285156/1/2022 0:00-23
285157/1/2022 0:00-12
285158/1/2022 0:0013
285159/1/2022 0:0014

 

  • Hello JImAyers ,
    you can try this approach instead:


    let
      Source = Table.FromRows(
        Json.Document(
          Binary.Decompress(
            Binary.FromText(
              "hdNBDoQgDAXQq0xYS2wLBfQqxvtfY4zGMMAfumBh8lIt338cLqQck1tcWHkVEvnQTnQ9P+dcqoid4Pv8CgUzWpHADGlE7oQfX1PMIRsQ4RZSlLWuS+O6r4imUFMkU2RTyCP4vwimiKZQUyRT5E7U5F5RgJBGbEC0yTEBElvCgGhL+msFBPxEiqKZiL5VfvxYVCtF2UxEXysvw7XBXglKZ9Ia1Ktrm/ML", 
              BinaryEncoding.Base64
            ), 
            Compression.Deflate
          )
        ), 
        let
          _t = ((type nullable text) meta [Serialized.Text = true])
        in
          type table [Employee = _t, Date = _t, #"Days Used" = _t, #"Should Be" = _t]
      ), 
      #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
          {"Employee", Int64.Type}, 
          {"Date", type datetime}, 
          {"Days Used", Int64.Type}, 
          {"Should Be", Int64.Type}
        }
      ), 
      #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Employee"}, 
        {
          {
            "Count", 
            each _, 
            type table [
              Employee = nullable number, 
              Date = nullable datetime, 
              Days Used = nullable number, 
              #"" = nullable text, 
              Shuld Be = nullable number
            ]
          }
        }
      ), 
      #"Added Custom1" = Table.AddColumn(
        #"Grouped Rows", 
        "Custom", 
        each List.Skip(
          List.Accumulate([Count][Days Used], {0}, (s, c) => s & {List.Min({List.Last(s) + c, 5})})
        )
      ), 
      #"Added Custom2" = Table.AddColumn(
        #"Added Custom1", 
        "Custom.1", 
        each Table.FromColumns(
          Table.ToColumns([Count]) & {[Custom]}, 
          Table.ColumnNames([Count]) & {"RT"}
        )
      ), 
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom2", {"Count", "Custom"}), 
      #"Expanded Custom.1" = Table.ExpandTableColumn(
        #"Removed Columns", 
        "Custom.1", 
        {"Date", "Days Used", "Should Be", "RT"}, 
        {"Date", "Days Used", "Should Be", "RT"}
      ), 
      #"Inserted Subtraction" = Table.AddColumn(
        #"Expanded Custom.1", 
        "Check", 
        each [Should Be] - [RT], 
        type number
      )
    in
      #"Inserted Subtraction"

2 Replies

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion

    Hello JImAyers ,
    you can try this approach instead:


    let
      Source = Table.FromRows(
        Json.Document(
          Binary.Decompress(
            Binary.FromText(
              "hdNBDoQgDAXQq0xYS2wLBfQqxvtfY4zGMMAfumBh8lIt338cLqQck1tcWHkVEvnQTnQ9P+dcqoid4Pv8CgUzWpHADGlE7oQfX1PMIRsQ4RZSlLWuS+O6r4imUFMkU2RTyCP4vwimiKZQUyRT5E7U5F5RgJBGbEC0yTEBElvCgGhL+msFBPxEiqKZiL5VfvxYVCtF2UxEXysvw7XBXglKZ9Ia1Ktrm/ML", 
              BinaryEncoding.Base64
            ), 
            Compression.Deflate
          )
        ), 
        let
          _t = ((type nullable text) meta [Serialized.Text = true])
        in
          type table [Employee = _t, Date = _t, #"Days Used" = _t, #"Should Be" = _t]
      ), 
      #"Changed Type" = Table.TransformColumnTypes(
        Source, 
        {
          {"Employee", Int64.Type}, 
          {"Date", type datetime}, 
          {"Days Used", Int64.Type}, 
          {"Should Be", Int64.Type}
        }
      ), 
      #"Grouped Rows" = Table.Group(
        #"Changed Type", 
        {"Employee"}, 
        {
          {
            "Count", 
            each _, 
            type table [
              Employee = nullable number, 
              Date = nullable datetime, 
              Days Used = nullable number, 
              #"" = nullable text, 
              Shuld Be = nullable number
            ]
          }
        }
      ), 
      #"Added Custom1" = Table.AddColumn(
        #"Grouped Rows", 
        "Custom", 
        each List.Skip(
          List.Accumulate([Count][Days Used], {0}, (s, c) => s & {List.Min({List.Last(s) + c, 5})})
        )
      ), 
      #"Added Custom2" = Table.AddColumn(
        #"Added Custom1", 
        "Custom.1", 
        each Table.FromColumns(
          Table.ToColumns([Count]) & {[Custom]}, 
          Table.ColumnNames([Count]) & {"RT"}
        )
      ), 
      #"Removed Columns" = Table.RemoveColumns(#"Added Custom2", {"Count", "Custom"}), 
      #"Expanded Custom.1" = Table.ExpandTableColumn(
        #"Removed Columns", 
        "Custom.1", 
        {"Date", "Days Used", "Should Be", "RT"}, 
        {"Date", "Days Used", "Should Be", "RT"}
      ), 
      #"Inserted Subtraction" = Table.AddColumn(
        #"Expanded Custom.1", 
        "Check", 
        each [Should Be] - [RT], 
        type number
      )
    in
      #"Inserted Subtraction"