Forum Discussion
TomEnns
4 years agoHelper I
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 value...
- 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"
smpa01
4 years agoCommunity Champion
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"