Transforming List Columns for Power BI Reporting

[OliverSch]

Hallo together,

In my Power BI report, I have a column that contains 'List' values (as seen in Power Query Editor). I want to represent the contents of these lists as a single, concatenated value within a visual. What is the recommended approach for this transformation?

Have anybody an idea how I can do that?

Thanks for your help.

Greetings

Oliver

[dufoq3]

Hi @OliverSch, another solution:

Before

After

Add this code as a new step (replace "Source" if necessary):

= Table.TransformColumns(Source, {}, each if _ is list then Text.Combine(List.Transform(_, Text.From), "|") else _)

Whole code with sample data:

let
    Source = #table(null, {
    {"a", {"text1", "text2", "text3"}, "b", {1,2,3,4,5}},
    {"a", {"text4", "text5", "text6", "text7"}, "b", {10,20}} }),
    Custom1 = Table.TransformColumns(Source, {}, each if _ is list then Text.Combine(List.Transform(_, Text.From), "|") else _)
in
    Custom1

[v-sshirivolu]

Hi @OliverSch ,

You can work with list columns in Power Query in several ways, depending on your reporting needs :

Convert a list to a text string for display or export:

Table.TransformColumns(
    YourTable,
    {
        "YourListColumn",
        each Text.Combine(List.Transform(_, Text.From), ","),
        type text
    }
)

This will change { "A", "B", "C" } to "A,B,C".

Expand the list into multiple rows for modeling or slicers:

Table.ExpandListColumn(YourTable, "YourListColumn")

This approach is best for filtering and relationships.

For both display and filtering:
Set up a many-to-many model by keeping your main table, creating a bridge table with the key and list items, and a dimension table of unique items, then relate them.

To handle multiple list columns automatically:

let
  listColumns = List.Select(
    Table.ColumnNames(YourTable),
    each Value.Is(Record.Field(YourTable{0}, _), type list)
  ),
  transformedTable = List.Accumulate(
    listColumns,
    YourTable,
    (state, current) =>
      Table.TransformColumns(
        state,
        {
          current,
          each Text.Combine(List.Transform(_, Text.From), ","),
          type text
        }
      )
  )
in
  transformedTable

This will convert all list-type columns to concatenated text.

If your lists contain records and you need a specific field:

Table.TransformColumns(
  #"Previous Step",
  {
    "YourListColumn",
    each if _ is list
         then Text.Combine(
                List.Transform(
                  List.RemoveNulls(_),
                  each Text.From(Record.Field(_, "lookupValue"))
                ),
                ", "
              )
         else 
    type text
  }
)

This extracts a chosen field (like "lookupValue") from each record in the list.

[v-sshirivolu]

Hi @OliverSch ,
I hope the information provided above assists you in resolving the issue. If you have any additional questions or concerns, please do not hesitate to contact us. We are here to support you and will be happy to help with any further assistance you may need.

[v-sshirivolu]

Hi @OliverSch ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

[v-sshirivolu]

Hi @OliverSch ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

[MarkLaf · Accepted solution]

Best practice depends on what you want in your report. Do you want a static combo of the list items? Or do you want to be able to slice by the list items (via a more dynamic M:M relationship)?

If you want both, you basically follow the (more complex) M:M model and then also do the list concatenation via whatever flavor makes the most sense in your situation: Power Query similar to outline below for static combo, calculated column after loading to semantic model (this is generally less optimal than Power Query), or you could get through a measure (best choice if the concatenation needs to be dynamic / responsive to certain filters, otherwise Power Query is the better choice, probably).

A simple example to highlight the decision.

Given this simple table:

#table( 
    type table [Id = Int64.Type, List = {text}], 
    {
        {1,{"A","B","C"}},
        {2,{"D","E","F"}}
    } 
)

An static combo would look like:

let
    Source = 
    #table( 
        type table [Id = Int64.Type, List = {text}], 
        {
            {1,{"A","B","C"}},
            {2,{"D","E","F"}}
        } 
    ),
    CombineListItems = 
    Table.TransformColumns(
        Source, 
        {"List", each Text.Combine(_, ","), type text}
    )
in
    CombineListItems

And in your report, a slicer on List would look like:

In other words, not useful as a slicer for most use cases, but if all you need is to display the concatenated list items as a static attribute, this is the way to go. This applies whether you are doing a conatenation of simple list items (as we are in this example) or otherwise expanding/unwrapping horizontally (i.e. not adding any rows as part of the transformation).

A more dynamic (M:M) setup would entail three tables - your original dimension, a new dimension constructed from all the list items in your List column, and a bridge with key pairs from the two dimensions:

Original

let
    Source = 
    #table( 
        type table [Id = Int64.Type, List = {text}], 
        {
            {1,{"A","B","C"}},
            {2,{"D","E","F"}}
        } 
    ),
    Select = Table.SelectColumns(Source,{"Id"})
in
    Select

List Dimension

let
    Source = 
    #table( 
        type table [Id = Int64.Type, List = {text}], 
        {
            {1,{"A","B","C"}},
            {2,{"D","E","F"}}
        } 
    ),
    Select = Table.SelectColumns(Source,{"List"}),
    ExpandListItems = Table.ExpandListColumn(Select, "List"),
    Distinct = Table.Distinct(ExpandListItems)
in
    Distinct

Bridge

let
    Source = 
    #table( 
        type table [Id = Int64.Type, List = {text}], 
        {
            {1,{"A","B","C"}},
            {2,{"D","E","F"}}
        } 
    ),
    ExpandListItems = Table.ExpandListColumn(Source, "List"),
    Distinct = Table.Distinct(ExpandListItems)
in
    Distinct

Now, with this setup, we have dimensions (Original and now also List Dimension) that will work in slicers. One caveat, is that a slicer filter is needed to enforce filtering from many to one (one to many filters apply automatically). For example, for Original to filter List Dimension, Original automatically passes filters to Bridge (as this is one to many), but for bridge to then pass that filter to List Dimension (many to one), we need to explicitly call this out with a measure filter. Quick snip showing this:

DAX of slicer filters for reference:

Slice by Original = CALCULATE( IF( NOT ISEMPTY( Original ), 1 ), Bridge )

Slice by List Dim = CALCULATE( IF( NOT ISEMPTY( 'List Dimension' ), 1 ), Bridge )

[Omid_Motamedise]

A list might include more than one value. What is your preferred aggregation operation to summarize them into a single value?

[anilgavhane]

@OliverSch

If you’d like help transforming list columns in Power Query for Power BI, here’s a streamlined guide:

Transforming List Columns in Power Query

1. Convert List to Text

To turn a list like { "A", "B", "C" } into "A, B, C":

Table.TransformColumns( YourTable, {{"YourListColumn", each Text.Combine(List.Transform(_, Text.From), ","), type text}} )

This is useful for display purposes or when exporting to visuals that expect text.

2. Expand List into Rows

If each list item should become its own row:

Table.ExpandListColumn(YourTable, "YourListColumn")

This is ideal for one-to-many relationships, like tags or categories.

3. Expand List into Columns

If the list has a fixed number of items and you want each item in its own column:

• Convert the list to text:

  Text.Combine(List.Transform(_, Text.From), ",")

  1. Then split it:

     Table.SplitColumn( YourTable, "YourListColumn", Splitter.SplitTextByDelimiter(","), {"Item1", "Item2", "Item3"} )

     4. Dynamic Handling of Multiple List Columns

     To process all list-type columns dynamically:

     let listColumns = List.Select( Table.ColumnNames(YourTable), each Value.Is(Record.Field(YourTable{0}, _), type list) ), transformedTable = List.Accumulate( listColumns, YourTable, (state, current) => Table.TransformColumns( state, {{current, each Text.Combine(List.Transform(_, Text.From), ","), type text}} ) ) in transformedTable

     This automatically finds and transforms all list columns to text.

[AmiraBedh]

Hello !

You can add a custom column in PQ and convert the list to text:

= let
    items = List.RemoveNulls([Tabelle]),        
    txt   = if List.IsEmpty(items) 
            then null 
            else Text.Combine(List.Transform(items, each Text.From(_)), ", ")
in
    txt

If the list contains records, extract a field first:

= let
    items = List.Transform([Tabelle], each Record.Field(_, "lookupValue")),
    items2 = List.RemoveNulls(items)
  in
    if List.IsEmpty(items2) then null else Text.Combine(List.Transform(items2, Text.From), ", ")

Then you convert the column in place:

= Table.TransformColumns(#"Previous Step",
    {{"Tabelle", each if _ is list 
       then Text.Combine(List.Transform(List.RemoveNulls(_), Text.From), ", ")
       else _, type text}})

[MasonMA]

@OliverSch 

I would suggest using 'Expand to New Rows' so that each list item becomes a row. (If concatenated, it’s just a string and you lose the ability to slice/filter on individuals)

This gives you a normalized fact-style table, which works nicely with DAX measures, slicers, and relationships.

From UI

Or M with Table.ExpandListColumn(Source, "Column")