Forum Discussion
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 contractual with a specific number of years. If this number of years is specified on the line, then it is at this period that the contract begins for the number of years indicated.
I'd like to be able to generate lines based on this "Basic service terms (year)" column. That is, if a cell contains the value 1, I'd like to be able to generate 3 income lines of the same amount for the 3 quarters following this reference line, taking the information from the machine.
For example, for the first line I'd like to duplicate 3 lines for machine N°06620 with 2024 Q2, 2024 Q3, 2024 Q4 with the value 6000, but I'd like the "Basic service terms (year)" to be null for these duplicated lines.
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.
7 Replies
Hi gfr ,
Please try this M code:let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAzMzJQ0lEyMjAyAVKBhkAChM0MDAyUYnWilUzMDcwN0RTgkzcCEsbICoA2mBjHG1qgmZFXmpMDpEzAymIB", 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 [#"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"I'm not sure if there will be any other numbers in your Basic service terms (year) besides 1, and I'm not sure if the Basic service terms (year) in your data all start at Q1, and if there are any that start at Q2, 3, or 4, so I'm taking as many scenarios as I can into account.
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.I'm facing an issue.
because if i consider another case where there are no null value and only line with service contract based on years. then the code will still generate a year line with a value for a quarter which doesn't exist.
Let's see with the following screen shots.
I'm facing an issue.
because if i consider another case where there are no null value and only line with service contract based on years. then the code will still generate a year line with a value for a quarter which doesn't exist.
Let's see with the following screen shots.
