Creating value lists with multiple values in same cell

%3CLINGO-SUB%20id%3D%22lingo-sub-1329481%22%20slang%3D%22en-US%22%3ECreating%20value%20lists%20with%20multiple%20values%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1329481%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20create%20value%20lists%20for%20fields%20from%20large%20tables%20of%20data%20where%20there%20are%20multiple%20values%20within%20the%20same%20cell%20separate%20by%20%22%3B%22.%20The%20data%20is%20contained%20in%20the%20table%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22563%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2265%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22166%22%3EColumn%201%3C%2FTD%3E%3CTD%20width%3D%22166%22%3EColumn%202%3C%2FTD%3E%3CTD%20width%3D%22166%22%3EColumn%203%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%201%3C%2FTD%3E%3CTD%3EValue1%3BValue2%3BValue3%3C%2FTD%3E%3CTD%3EValueA%3C%2FTD%3E%3CTD%3EValuei%3BValueii%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%202%3C%2FTD%3E%3CTD%3EValue1%3C%2FTD%3E%3CTD%3EValueA%3BValueB%3BValueC%3C%2FTD%3E%3CTD%3EValuei%3BValueii%3BValueiii%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%203%3C%2FTD%3E%3CTD%3EValue1%3BValue4%3BValue5%3C%2FTD%3E%3CTD%3EValueB%3BValueD%3C%2FTD%3E%3CTD%3EValueiii%3BValueiv%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%204%3C%2FTD%3E%3CTD%3EValue2%3BValue6%3C%2FTD%3E%3CTD%3EValueD%3BValueE%3C%2FTD%3E%3CTD%3EValueiv%3BValuev%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERow%205%3C%2FTD%3E%3CTD%3EValue3%3BValue7%3C%2FTD%3E%3CTD%3EValueF%3C%2FTD%3E%3CTD%3EValuevi%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20wish%20to%20generate%20the%20following%20value%20lists%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22498%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22166%22%3EColumn%201%20Value%20List%3C%2FTD%3E%3CTD%20width%3D%22166%22%3EColumn%202%20Value%20List%3C%2FTD%3E%3CTD%20width%3D%22166%22%3EColumn%203%20Value%20List%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue1%3C%2FTD%3E%3CTD%3EValueA%3C%2FTD%3E%3CTD%3EValuei%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue2%3C%2FTD%3E%3CTD%3EValueB%3C%2FTD%3E%3CTD%3EValueii%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue3%3C%2FTD%3E%3CTD%3EValueC%3C%2FTD%3E%3CTD%3EValueiii%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue4%3C%2FTD%3E%3CTD%3EValueD%3C%2FTD%3E%3CTD%3EValueiv%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue5%3C%2FTD%3E%3CTD%3EValueE%3C%2FTD%3E%3CTD%3EValuev%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue6%3C%2FTD%3E%3CTD%3EValueF%3C%2FTD%3E%3CTD%3EValuevi%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EValue7%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20do%20this%20without%20disrupting%20the%20original%20data%20table.%20Originally%20I%20thought%20I%20might%20be%20able%20to%20delimit%20the%20data%20within%20Pivot%20Tables%2C%20but%20inquiries%20I've%20made%20so%20far%20indicate%20that%20the%20data%20needs%20to%20be%20delimited%20first%20before%20generating%20the%20Pivot%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20anyone%20have%20any%20proposed%20solutions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1329481%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1330078%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20value%20lists%20with%20multiple%20values%20in%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1330078%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F632935%22%20target%3D%22_blank%22%3E%40pacecar81%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20done%20by%20Power%20Query.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20438px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F185978iBF221F1BE124DA4E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ELet%20name%20the%20source%20data%20as%20Range%2C%20query%20it%2C%20unpivot%20all%20columns%2C%20split%20texts%20to%20lists%2C%20groups%20by%20columns%20with%20sum%20aggregation%20(or%20any%20other)%20and%20change%20in%20formula%20List.Sum%20on%20conversion%20to%20sequential%20list%20(see%20script)%2C%20create%20final%20table%20based%20on%20result.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20script%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Range%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20PromotHeaders%20%3D%20Table.PromoteHeaders(%0A%20%20%20%20%20%20%20%20Source%2C%0A%20%20%20%20%20%20%20%20%5BPromoteAllScalars%3Dtrue%5D%0A%20%20%20%20)%2C%0A%20%20%20%20UnpivotColumns%20%3D%20Table.UnpivotOtherColumns(%0A%20%20%20%20%20%20%20%20PromotHeaders%2C%0A%20%20%20%20%20%20%20%20%7B%7D%2C%0A%20%20%20%20%20%20%20%20%22Attribute%22%2C%20%22Value%22%0A%20%20%20%20)%2C%0A%20%20%20%20TextToList%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20UnpivotColumns%2C%0A%20%20%20%20%20%20%20%20%22Custom%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.Split(%5BValue%5D%2C%22%3B%22)%0A%20%20%20%20)%2C%0A%20%20%20%20GroupRows%20%3D%20Table.Group(%0A%20%20%20%20%20%20%20%20TextToList%2C%0A%20%20%20%20%20%20%20%20%7B%22Attribute%22%7D%2C%0A%20%20%20%20%20%20%20%20%7B%7B%22Count%22%2C%20each%20List.Distinct(List.Sort(List.Combine(%5BCustom%5D)))%7D%7D%0A%20%20%20%20)%2C%0A%20%20%20%20CreateTable%20%3D%20Table.FromColumns(%0A%20%20%20%20%20%20%20%20GroupRows%5BCount%5D%2C%0A%20%20%20%20%20%20%20%20GroupRows%5BAttribute%5D%0A%20%20%20%20)%0Ain%0A%20%20%20%20CreateTable%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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 1Column 2Column 3
Row 1Value1;Value2;Value3ValueAValuei;Valueii
Row 2Value1ValueA;ValueB;ValueCValuei;Valueii;Valueiii
Row 3Value1;Value4;Value5ValueB;ValueDValueiii;Valueiv
Row 4Value2;Value6ValueD;ValueEValueiv;Valuev
Row 5Value3;Value7ValueFValuevi

 

And I wish to generate the following value lists:

 

Column 1 Value ListColumn 2 Value ListColumn 3 Value List
Value1ValueAValuei
Value2ValueBValueii
Value3ValueCValueiii
Value4ValueDValueiv
Value5ValueEValuev
Value6ValueFValuevi
Value7  

 

Is there a way to do this without disrupting the original data table. Originally I thought I might be able to delimit the data within Pivot Tables, but inquiries I've made so far indicate that the data needs to be delimited first before generating the Pivot Table.

 

Does anyone have any proposed solutions?

 

Thanks in advance.

1 Reply
Highlighted

@pacecar81 

That could be done by Power Query.

image.png

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