Forum Discussion

Lumegu's avatar
Lumegu
Frequent Visitor
4 years ago
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 Sand...
  • Anonymous's avatar
    Anonymous
    4 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