Forum Discussion

Rule83's avatar
Rule83
New Member
9 years ago
Solved

Unique and consecutive values count and list to one string

Here is the thing. Maybe due to of all the (probable) possibilities I can't figure out exactly how to do the following. My preference is to do it via the query editor (M). This to I can (re)use and m...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Sure:

     

    let
    Source = Data,
    AddedOriginalSort = Table.AddIndexColumn(Source, "OriginalSort", 1, 1),
    Grouped = Table.Group(AddedOriginalSort, {"Names"}, {{"GroupedData", each _, type table}, {"CountALL", each Table.RowCount(_), type number}, {"ValuesALL", each Text.Combine([Value]), type text}}),
    AddedCountCONS = Table.TransformColumns(Grouped,{{"GroupedData", each Table.AddIndexColumn(_,"CountCONS",1,1)}}),
    ExpandedGroupedData = Table.ExpandTableColumn(AddedCountCONS, "GroupedData", {"Value", "OriginalSort", "CountCONS"}),
    OriginalSort = Table.Sort(ExpandedGroupedData,{{"OriginalSort", Order.Ascending}}),
    RemovedOroginalSort = Table.RemoveColumns(OriginalSort,{"OriginalSort"}),
    AddedValuesCONS = Table.AddColumn(RemovedOroginalSort, "ValuesCONS", each Text.Start([ValuesALL],[CountCONS])),
    AddedCombination = Table.AddColumn(AddedValuesCONS, "Combination", each if Text.Contains([ValuesALL],"X") and Text.Contains([ValuesALL],"Y") then "YES" else "NO"),
    ReorderedColumns = Table.ReorderColumns(AddedCombination,{"Names", "Value", "CountCONS", "CountALL", "ValuesCONS", "ValuesALL", "Combination"})
in
    ReorderedColumns
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

Sure:

 

let
    Source = Data,
    AddedOriginalSort = Table.AddIndexColumn(Source, "OriginalSort", 1, 1),
    Grouped = Table.Group(AddedOriginalSort, {"Names"}, {{"GroupedData", each _, type table}, {"CountALL", each Table.RowCount(_), type number}, {"ValuesALL", each Text.Combine([Value]), type text}}),
    AddedCountCONS = Table.TransformColumns(Grouped,{{"GroupedData", each Table.AddIndexColumn(_,"CountCONS",1,1)}}),
    ExpandedGroupedData = Table.ExpandTableColumn(AddedCountCONS, "GroupedData", {"Value", "OriginalSort", "CountCONS"}),
    OriginalSort = Table.Sort(ExpandedGroupedData,{{"OriginalSort", Order.Ascending}}),
    RemovedOroginalSort = Table.RemoveColumns(OriginalSort,{"OriginalSort"}),
    AddedValuesCONS = Table.AddColumn(RemovedOroginalSort, "ValuesCONS", each Text.Start([ValuesALL],[CountCONS])),
    AddedCombination = Table.AddColumn(AddedValuesCONS, "Combination", each if Text.Contains([ValuesALL],"X") and Text.Contains([ValuesALL],"Y") then "YES" else "NO"),
    ReorderedColumns = Table.ReorderColumns(AddedCombination,{"Names", "Value", "CountCONS", "CountALL", "ValuesCONS", "ValuesALL", "Combination"})
in
    ReorderedColumns
  • Rule83's avatar
    Rule83
    New Member
    9 years ago

    Superb! I'll figure out how you've build it up and test it.