Transforming List Columns for Power BI Reporting

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

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.

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")

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}})

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.

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