Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
JImAyers
Helper I
Helper I

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

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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

View solution in original post

2 REPLIES 2
ImkeF
Super User
Super User

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

@ImkeF 

Your solution is elegant and solves my problem. Thank You!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.