Forum Discussion

jlynch's avatar
jlynch
Frequent Visitor
9 years ago
Solved

calculated column to display comma separated values as the text lookup value for each value

I have two tables, one lists the text string values and an ID, the other has a comma separated list of ID's, i want to display a concatenated text list in a single cell based on the ID's in the main table, so:

 

Table 1

ID        Text Value

 

Table 2

Lookup value to Table 1 = comma separated IDs

 

what we need is comma separated text values.

Tutorial Requests

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    I believe what you want is a VALUES and CONCATENATEX formula

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi jlynch

     

    I think I follow what you need, but just to be sure, can you please mock up a small sample of fake data for your two tables including your expected result.  We can try and help you with the middle part. :)

    • MarcelBeug's avatar
      MarcelBeug
      Icon for Community Champion rankCommunity Champion
      9 years ago

      This is my interpretation and Power Query solution:

       

       

      CSTV query code:

       

      let
    Source = Table2,

    // Get nested lists of ID numbers
    SplitToIDNumbers = Table.TransformColumns(Source,{},each List.Transform(Text.Split(_,","),Number.From)),

    // Add index as a grouping for the IDs after expansion
    AddedIndex = Table.AddIndexColumn(SplitToIDNumbers, "Index", 0, 1),
    ExpandedIDs = Table.ExpandListColumn(AddedIndex, "IDs"),

    // Get the Text Values from table1
    MergedWithTable1 = Table.NestedJoin(ExpandedIDs,{"IDs"},Table1,{"ID"},"NewColumn",JoinKind.LeftOuter),

    // Expand the nested tables
    ExpandedTextValues = Table.ExpandTableColumn(MergedWithTable1, "NewColumn", {"Text Value"}, {"Text Value"}),

    // Use Transform - Group By to create base code (e.g. use group function "Sum"  for the text values, then adjust the code to Text.Combine
    Grouped = Table.Group(ExpandedTextValues, {"Index"}, {{"Text Values", each Text.Combine([Text Value],","), type text}}),

    RemovedIndex = Table.RemoveColumns(Grouped,{"Index"})
in
    RemovedIndex

      :

       

      • jlynch's avatar
        jlynch
        Frequent Visitor
        9 years ago

        Marcel...

        the example is perfect, the solutions I'm not sure of, I'm in Power BI, I don't know M so I'm not sure, do I go to the Query Editor and there enter the formulas you indicated, is this a measure or a column?  Sorry to be a bit slow on this one, but I think the process is just beyond my skill level?

    • jlynch's avatar
      jlynch
      Frequent Visitor
      9 years ago

      Marcel below has interpretted what i need accurately, I'm going to attempt his solution but it's a bit more of a code solution than I'm used to.