Forum Discussion

TomEnns's avatar
TomEnns
Helper I
4 years ago
Solved

Way to itterate loop through rows with subtraction. (PowerQuery)

This is similar to another post I made which I have since deleted as it was too complex for my needs.  I am looking for a way to loop through a table and merge in a date table while subtracting values from a specific column of the first table as I go. This will make more sensse with images I mocked the tables up in simplified form in excel to make the idea make sense.

 

Table 1

Table 2

I want to merge these two tables in such a way that this is the resulting output table:

You will notice I have essentially merged in the date table starting over for each project and resource. Then I reitereated ech line to max out at 1 day until there were no days left from the roginal table.

 

Any ideas on how to do this in PowerQuery would be awesome.

 

Thanks!

 

 

Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    TomEnns  with PQ

    let
   minDate = List.Min(Calendar[Date]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjy9zJU0lEyNASRJnqmSrE6CFFTU1OQHFzMGMgzMjICksZwMRMQzxgsAxczhZtoBDIxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectKey = _t, Resource = _t, Days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectKey", type text}, {"Resource", Int64.Type}, {"Days", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectKey", "Resource"}, {{"ad", each _, type table [ProjectKey=nullable text, Resource=nullable number, Days=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad],
Terminate = Number.RoundUp(x{0}[Days])
    ,Loop = List.Generate(
         ()=>[i=0,j=1,k=Calendar[Date]{i}],
         each [i]<Terminate,
         each [i=[i]+1,j=if [j]+1<Terminate then [j]+1 else x[Days]{0}-i , k=Calendar[Date]{i}],
         each [[j],[k]]
    ),
    #"Converted to Table" = Table.FromList(Loop, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"j", "k"}, {"j", "k"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"j", "Number of Days"}, {"k", "Date"}}) in #"Renamed Columns"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Number of Days"}, {"Date", "Number of Days"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}, {"Number of Days", type number}})
in
    #"Changed Type1"

     

     

     

     

3 Replies

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    TomEnns  with PQ

    let
   minDate = List.Min(Calendar[Date]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjy9zJU0lEyNASRJnqmSrE6CFFTU1OQHFzMGMgzMjICksZwMRMQzxgsAxczhZtoBDIxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectKey = _t, Resource = _t, Days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectKey", type text}, {"Resource", Int64.Type}, {"Days", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectKey", "Resource"}, {{"ad", each _, type table [ProjectKey=nullable text, Resource=nullable number, Days=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad],
Terminate = Number.RoundUp(x{0}[Days])
    ,Loop = List.Generate(
         ()=>[i=0,j=1,k=Calendar[Date]{i}],
         each [i]<Terminate,
         each [i=[i]+1,j=if [j]+1<Terminate then [j]+1 else x[Days]{0}-i , k=Calendar[Date]{i}],
         each [[j],[k]]
    ),
    #"Converted to Table" = Table.FromList(Loop, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"j", "k"}, {"j", "k"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"j", "Number of Days"}, {"k", "Date"}}) in #"Renamed Columns"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ad"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Number of Days"}, {"Date", "Number of Days"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Date", type date}, {"Number of Days", type number}})
in
    #"Changed Type1"

     

     

     

     

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    Here's a version that uses table expansion rather than a loop.

    let
    Calendar= List.Dates(#date(2022,1,7), 24, #duration(1,0,0,0)),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCgjy9zJU0lEyNASRJnqmSrE6CFFTU1OQHFzMGMgzMjICksZwMRMQzxgsAxczhZtoBDIxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectKey = _t, Resource = _t, Days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectKey", type text}, {"Resource", Int64.Type}, {"Days", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each
        Table.FromColumns(
            {
                List.FirstN(Calendar, Number.RoundUp([Days])),
                List.Repeat({1}, Number.RoundUp([Days]) - 1)
                   & {Number.RoundDown([Days]) - Number.RoundUp([Days]) + Number.Mod([Days], 1) + 1}
            },
            {"Date", "NumOfDays"}
            ),
        type table),
    #"Expanded Date" = Table.ExpandTableColumn(#"Added Custom", "Date", {"Date", "NumOfDays"}, {"Date", "NumOfDays"})
in
    #"Expanded Date"

     

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    TomEnns you already have an amazing solution from great brains, here is my take at it:

     

     

    let
    MinDate = List.Min(Date[Date]),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKzzJU0lEyNASRJnqmSrE6CFFTU1OQHFzMGMgzMjICksZwMRMQzxgsAxczhZtoBDIxFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Resources = _t, Days = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Resources", Int64.Type}, {"Days", type number}}),

    //fun starts here

    //first core step
    #"Added Date Range" = Table.AddColumn(#"Changed Type", "Date", each  {Number.From(MinDate)..(Number.From(MinDate)+ Number.RoundUp([Days])-1)}),

    #"Expanded Date Range" = Table.ExpandListColumn(#"Added Date Range", "Date"),
    #"Changed Date Type" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date", type date}}),
    
    //second core step
    #"Added Number of Days" = Table.AddColumn(#"Changed Date Type", "Num of Days", each  if (Duration.Days([Date]-MinDate)+1)-[Days] <= 0 then 1 else (Duration.Days([Date]-MinDate)+1)-[Days], type number)

    //done
in
    #"Added Number of Days"