Forum Discussion

pacecar81's avatar
pacecar81
Copper Contributor
Apr 22, 2020

Creating value lists with multiple values in same cell

I need to create value lists for fields from large tables of data where there are multiple values within the same cell separate by ";". The data is contained in the table as follows:     Column...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 22, 2020

pacecar81 

That could be done by Power Query.

Let name the source data as Range, query it, unpivot all columns, split texts to lists, groups by columns with sum aggregation (or any other) and change in formula List.Sum on conversion to sequential list (see script), create final table based on result.

 

The script is

let
    Source = Excel.CurrentWorkbook(){[Name="Range"]}[Content],
    PromotHeaders = Table.PromoteHeaders(
        Source,
        [PromoteAllScalars=true]
    ),
    UnpivotColumns = Table.UnpivotOtherColumns(
        PromotHeaders,
        {},
        "Attribute", "Value"
    ),
    TextToList = Table.AddColumn(
        UnpivotColumns,
        "Custom",
        each Text.Split([Value],";")
    ),
    GroupRows = Table.Group(
        TextToList,
        {"Attribute"},
        {{"Count", each List.Distinct(List.Sort(List.Combine([Custom])))}}
    ),
    CreateTable = Table.FromColumns(
        GroupRows[Count],
        GroupRows[Attribute]
    )
in
    CreateTable

Resources