Forum Discussion
Lumegu
4 years agoFrequent Visitor
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 Sand...
- Anonymous4 years ago
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
Anonymous
4 years agoNot applicable
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