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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors