Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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 |
Solved! Go to Solution.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
12 | |
11 | |
11 | |
8 |
User | Count |
---|---|
45 | |
28 | |
14 | |
14 | |
13 |