Generate row based on column criteria

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.