Forum Discussion
Solved
Generate row based on column criteria
Dear all, I have a data table where we can find machine numbers and service sales (in amounts) by year and quarter. In the column "Basic service terms (year)" we find out if this sale is contract...
Hi gfr ,
Sorry for the slow reply, I'm on vacation. Please try the following M code.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAzMzJQ0lEyMjAyAVKBRkACxDAzMDBQitXBVGBMSIEhkDBEVmBibmBuiKYgrzQnB58akDOM0WwxMY43tMBujgnZjkG1KBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Machine number" = _t, #"Year/Quarter.1" = _t, #"Year/Quarter.2" = _t, #"Basic service terms (year)" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine number", type text}, {"Year/Quarter.1", Int64.Type}, {"Year/Quarter.2", type text}, {"Basic service terms (year)", Int64.Type}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Basic service terms (year)"]<>null then List.Numbers(0,[#"Basic service terms (year)"]*4,1) else null), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), #"Expanded Custom" = Table.ExpandListColumn(#"Added Index", "Custom"), #"Replaced Value1" = Table.ReplaceValue(#"Expanded Custom",null,0,Replacer.ReplaceValue,{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Replaced Value1", "Custom.1", each let a=Number.FromText(Text.Middle([#"Year/Quarter.2"],1)), b=a+[Custom], c= if [Custom]=null then a else if b>4 then b-Number.RoundDown(b/4,0)*4 else b in if c=0 then 4 else c), #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [#"Basic service terms (year)"],each if [Custom]=0 then [#"Basic service terms (year)"] else null,Replacer.ReplaceValue,{"Basic service terms (year)"}), #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Year", each let a=Table.RowCount(Table.SelectRows(#"Replaced Value",(x)=>x[Machine number]=[Machine number] and x[Custom.1]=4 and x[Custom]<[Custom] and x[Index]=[Index])) in a+[#"Year/Quarter.1"]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each "Q"&Text.From([Custom.1])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Year/Quarter.1", "Year/Quarter.2", "Custom", "Custom.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Machine number", "Year", "Custom.2", "Basic service terms (year)", "Value"}) in #"Reordered Columns"I merged all the previous data situations into one dataset:
Since the output is too long for me to take a screenshot for you, I've posted the pbix file below.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi gfr ,
Sorry for being late.
Please try this:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAzMzJQ0lEyMjAyAVKBhkAChM0MDAyUYnWilUzMDcwNkRQYAQljuIJYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Machine number" = _t, #"Year/Quarter.1" = _t, #"Year/Quarter.2" = _t, #"Basic service terms (year)" = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine number", type text}, {"Year/Quarter.1", Int64.Type}, {"Year/Quarter.2", type text}, {"Basic service terms (year)", Int64.Type}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Basic service terms (year)"]<>null then List.Numbers(0,[#"Basic service terms (year)"]*4,1) else null),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Replaced Value1" = Table.ReplaceValue(#"Expanded Custom",null,0,Replacer.ReplaceValue,{"Custom"}),
#"Added Custom1" = Table.AddColumn(#"Replaced Value1", "Custom.1", each let
a=Number.FromText(Text.Middle([#"Year/Quarter.2"],1)),
b=a+[Custom],
c= if [Custom]=null then a else if b>4 then b-Number.RoundDown(b/4,0)*4 else b
in if c=0 then 4 else c),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [#"Basic service terms (year)"],each if [Custom]=0 then [#"Basic service terms (year)"] else null,Replacer.ReplaceValue,{"Basic service terms (year)"}),
#"Added Custom2" = Table.AddColumn(#"Replaced Value", "Year", each let a=Table.RowCount(Table.SelectRows(#"Replaced Value",(x)=>x[Machine number]=[Machine number] and x[Custom.1]=[Custom.1] and x[Custom]<[Custom]))
in if [Custom.1]=1 and [Custom]<>0 then [#"Year/Quarter.1"]+a+1 else [#"Year/Quarter.1"]+a),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each "Q"&Text.From([Custom.1])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Year/Quarter.1", "Year/Quarter.2", "Custom", "Custom.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Machine number", "Year", "Custom.2", "Basic service terms (year)", "Value"})
in
#"Reordered Columns"
Here is my sample data:
The final output is below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi again Dino,
It works !! but the thing is if we consider two contract which overlapped as we can see on the below picture:
We can see on the result it jumps for example directly to 2025 on first contract and every two quarter it changes the year on the second
Hi gfr ,
Sorry for the slow reply, I'm on vacation. Please try the following M code.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAzMzJQ0lEyMjAyAVKBRkACxDAzMDBQitXBVGBMSIEhkDBEVmBibmBuiKYgrzQnB58akDOM0WwxMY43tMBujgnZjkG1KBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Machine number" = _t, #"Year/Quarter.1" = _t, #"Year/Quarter.2" = _t, #"Basic service terms (year)" = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine number", type text}, {"Year/Quarter.1", Int64.Type}, {"Year/Quarter.2", type text}, {"Basic service terms (year)", Int64.Type}, {"Value", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Basic service terms (year)"]<>null then List.Numbers(0,[#"Basic service terms (year)"]*4,1) else null), #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Index", 1, 1, Int64.Type), #"Expanded Custom" = Table.ExpandListColumn(#"Added Index", "Custom"), #"Replaced Value1" = Table.ReplaceValue(#"Expanded Custom",null,0,Replacer.ReplaceValue,{"Custom"}), #"Added Custom1" = Table.AddColumn(#"Replaced Value1", "Custom.1", each let a=Number.FromText(Text.Middle([#"Year/Quarter.2"],1)), b=a+[Custom], c= if [Custom]=null then a else if b>4 then b-Number.RoundDown(b/4,0)*4 else b in if c=0 then 4 else c), #"Replaced Value" = Table.ReplaceValue(#"Added Custom1",each [#"Basic service terms (year)"],each if [Custom]=0 then [#"Basic service terms (year)"] else null,Replacer.ReplaceValue,{"Basic service terms (year)"}), #"Added Custom2" = Table.AddColumn(#"Replaced Value", "Year", each let a=Table.RowCount(Table.SelectRows(#"Replaced Value",(x)=>x[Machine number]=[Machine number] and x[Custom.1]=4 and x[Custom]<[Custom] and x[Index]=[Index])) in a+[#"Year/Quarter.1"]), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.2", each "Q"&Text.From([Custom.1])), #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Year/Quarter.1", "Year/Quarter.2", "Custom", "Custom.1"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Machine number", "Year", "Custom.2", "Basic service terms (year)", "Value"}) in #"Reordered Columns"I merged all the previous data situations into one dataset:
Since the output is too long for me to take a screenshot for you, I've posted the pbix file below.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.There are no accept as a solution button on this last message but yeah thanks a lot it works so we can consider it as a solution !!!
Thanks a lot again