Forum Discussion
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"
| Employee | Date | Days Used | Should Be |
| 36746 | 3/1/2022 0:00 | 0 | 0 |
| 36746 | 4/1/2022 0:00 | 1 | 1 |
| 36746 | 5/1/2022 0:00 | 0 | 1 |
| 36746 | 6/1/2022 0:00 | 1 | 2 |
| 36746 | 7/1/2022 0:00 | -1 | 1 |
| 36746 | 8/1/2022 0:00 | 1 | 2 |
| 36746 | 9/1/2022 0:00 | 1 | 3 |
| 28515 | 3/1/2020 0:00 | 0 | 0 |
| 28515 | 4/1/2020 0:00 | 0 | 0 |
| 28515 | 5/1/2020 0:00 | 0 | 0 |
| 28515 | 6/1/2020 0:00 | 0 | 0 |
| 28515 | 7/1/2020 0:00 | 0 | 0 |
| 28515 | 2/1/2021 0:00 | 0 | 0 |
| 28515 | 3/1/2021 0:00 | 0 | 0 |
| 28515 | 4/1/2021 0:00 | 0 | 0 |
| 28515 | 5/1/2021 0:00 | 0 | 0 |
| 28515 | 6/1/2021 0:00 | 0 | 0 |
| 28515 | 7/1/2021 0:00 | 1 | 1 |
| 28515 | 8/1/2021 0:00 | 1 | 2 |
| 28515 | 9/1/2021 0:00 | 1 | 3 |
| 28515 | 10/1/2021 0:00 | 1 | 4 |
| 28515 | 11/1/2021 0:00 | 1 | 5 |
| 28515 | 12/1/2021 0:00 | 1 | 5 |
| 28515 | 1/1/2022 0:00 | 1 | 5 |
| 28515 | 2/1/2022 0:00 | 1 | 5 |
| 28515 | 3/1/2022 0:00 | -1 | 4 |
| 28515 | 4/1/2022 0:00 | 1 | 5 |
| 28515 | 5/1/2022 0:00 | 1 | 5 |
| 28515 | 6/1/2022 0:00 | -2 | 3 |
| 28515 | 7/1/2022 0:00 | -1 | 2 |
| 28515 | 8/1/2022 0:00 | 1 | 3 |
| 28515 | 9/1/2022 0:00 | 1 | 4 |
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
Community 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"