Transforming List Columns for Power BI Reporting

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 )