March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch 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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.