Split rows based on condition

[Lumegu]

Dear community,

I have a question for which I was hoping you could perhaps help out, I have the following dataset:

Name	Date	Hours	Criteria 1	Criteria 2	Criteria 3	Criteria 4
Sander	1-1-2022	8	X
Sander	1-2-2022	8		X	X
Sander	1-3-2022	8			X
Sander	1-4-2022	8

Whenever in one of the criteria columns there are multiple "X's" on the same date I would like split that line into the same amount of rows equal to the amount of "X's" keeping the rest of the data in the other columns the same:

Name	Date	Hours	Criteria 1	Criteria 2	Criteria 3	Criteria 4
Sander	1-1-2022	8	X
Sander	1-2-2022	8		X
Sander	1-2-2022	8			X
Sander	1-3-2022	8			X
Sander	1-4-2022	8

I was hoping somebody could help me out how to transform this dataset properly?

Best regards!

[Anonymous · Accepted solution]

nice exercise

let
    
    selX= (tab) => Table.SelectRows(tab, each (List.Contains(Record.FieldValues(_), "X"))),
    
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MS0ktUtJRMtQ31DcyMDICMi2AOAKIoShWB0WZEUyZKUQ+AqYaTZ0xTJ053ChsykyQbUVSCVaWn5ualJ9SCRQwQphngmwpFqUmCKWWyMqwKDXVN4Tbb4TmzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Hours = _t, #"Criteria 1" = _t, #"Criteria 2" = _t, #"Criteria 3" = _t, #"Criteria 4" = _t]),
    #"UnPivot" = Table.UnpivotOtherColumns(Origine, {"Name", "Date", "Hours"}, "Attributo", "Valore"),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(#"UnPivot", "Indice", 1, 1, Int64.Type),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna indice", {"Name", "Date", "Hours"}, {{"all", each selX(Table.Pivot(_, List.Distinct(_[Attributo]), "Attributo", "Valore", (x)=>x{0}? ))}}),
    #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"Criteria 1", "Criteria 2", "Criteria 3", "Criteria 4"}, {"Criteria 1", "Criteria 2", "Criteria 3", "Criteria 4"}),
    #"Sostituito valore" = Table.ReplaceValue(#"Tabella all espansa",null,"",Replacer.ReplaceValue,{"Criteria 1", "Criteria 2", "Criteria 3", "Criteria 4"})
in
    #"Sostituito valore"

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MS0ktUtJRMtQ31DcyMDICMi2AOAKIoShWB0WZEUyZKUQ+AqYaTZ0xTJ053ChsykyQbUVSCVaWn5ualJ9SCRQwQphngmwpFqUmCKWWyMqwKDXVN4Tbb4TmzFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Hours = _t, #"Criteria 1" = _t, #"Criteria 2" = _t, #"Criteria 3" = _t, #"Criteria 4" = _t]),
    #"UnPivot" = Table.UnpivotOtherColumns(Origine, {"Name", "Date", "Hours"}, "Attributo", "Valore"),
    #"indice" = Table.AddIndexColumn(#"UnPivot", "Indice", 1, 1, Int64.Type),
    pivot = Table.Pivot(#"indice", List.Distinct(#"indice"[Attributo]), "Attributo", "Valore", (x)=>x{0}? ?? "" ),
    clean=Table.SelectRows(pivot, each (List.Contains(Record.FieldValues(_), "X")))
in
   clean

[Vijay_A_Verma]

Solution file uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuShLXB7Qm8xdl0wJ?e=7Fl6WB 

Below is M-code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Hours", Int64.Type}, {"Criteria 1", type text}, {"Criteria 2", type text}, {"Criteria 3", type text}, {"Criteria 4", type any}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name", "Date", "Hours"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Text.From([Date])&"-"&Text.From([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
    #"Removed Columns1"