In visual: expand list to rows

[FabvE]

Hi all,

I have a query which holds a column with email addresses. These are stored as a list in each row.

The [MembersList] is generated by splitting the column [Members] (which holds mail addresses as plain text separated by comma).

When I put this column into a visual it only shows the value as [List] but not the includes mail addresses.

Even when I select a row in another visual, which filters the page to one entry, the table visual is still showing [List].

Question: how can I show the content of the list field in a table visual after selecting a row in another visual?

Bonus: I want to have a filter with the distinct mail addresses (I already have a linked query which comes from above mentioned [Members] -> splitted by comma into rows -> remove double entries -> sort ascending) and only want entries on the page which contain the selected mail in [MembersList].

Sorry for posting daily, I'm fairly new to PBI. 😅

Thanks in advance!

[HotChilli]

Power Query can handle a column of lists like this. Powerbi front end cannot.

So you have to do more steps in Power Query to get what you want.

This will either be 'expand column'-> to new rows   or 'expand column' (so that it's just the plain text again - in which case you would have stopped at the previous step because you already had this format))

[FabvE]

Meanwhile I did another approach:

I kept the lists in the rows and created two extra queries: tblOwners and tblMembers.

Each i linked to the ID and list-column in the source table. Each query is then expanded and sorted.

Finally I created 1:n relations between the source table and the new querytables.

Is this a much cleaner data model?

[Anonymous · Accepted solution]

Hi @FabvE ,

Keeping lists in rows and creating separate queries prevents the number of rows in the main table from spiking, resulting in a neater, more manageable data model.

This approach improves performance because you don't have to deal with a large number of rows in a single table. Instead, you can utilize relationships to manage data more efficiently.

Having separate tables for owners and members allows for more flexible and targeted queries and visualizations.

As a result, your new approach does seem to result in a cleaner, more efficient data model. By separating concerns and usage relationships, you maintain the integrity of your data while making it easier to manage and visualize.

Model relationships in Power BI Desktop - Power BI | Microsoft Learn

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[FabvE]

Hi,

thank you for the reply.

I rearranged my data model now like this:

The Software ID is unique and manages the relationship between newly created queries for members, owner and software activity.

Portfolio ist the base table where the other queries are created from and hosts additional informations.

Would this be a better data model?

Thank you in advance!

[FabvE]

Ok, I understand.

But then what's the advantage of having a list as "value" in a row? I thought it is good to keep the report clean and compact, counting the contained values is easy but other stuff like filtering and displaying in a visual seems a pain? Besides the [Members] there's also a [Owner] column with a list.

Initially my query has 197 entries, with both list-columns expanded the query grows up to 1475 entries...

Now I alo need a helper table to host the IDs as unique values because these are now multiplied with the "extraction" of the two list columns.