Forum Discussion
Solved
Split rows based on condition
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!
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
2 Replies
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 cleanSolution 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"
