View contents of List and analysis

[willszee]

Hi Everyone, 

I have 9 columns from a total of 26 that are displayed as "List" in Power BI.

The source of the data is from a MS List service.

The 9 columns are multi select choice options. The number of items available to select range from 5 - 23 options across these lists.

Within Power BI I wish to be able to analysis the values in each list and display various outputs. 

I have tried to expand the column "Medications". When selection "Expand to New Rows" across 9 columns the number of rows grow from 220 to 500 million rows. 

When I select the "Extract Values" I can see that the items are concatenated, my 1st positive step. 

How can I now analysis these values and sum or average etc. these when I am trying to report in a visual output?

Thank you in advance.

David.

[v-hashadapu]

Hi @willszee , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @willszee , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[willszee]

Hi v-hashadapu,

Thanks very much for contributing to my request for some guidance. I will attempt to follow the guidance and let you know how I get on over the coming days.

Regards,

willszee

[v-hashadapu]

Hi @willszee , thank you for reaching out to the Microsoft Fabric Community Forum.

Thanks for the update. Hope your issue gets resolved soon. when it does, please share the insights here and mark it or any other helpful answer 'Accept as Solution', which will help others with similar queries.

Thank you.

[Akash_Varuna]

Hi @willszee As you have already Expanded the list and i suppose cleaned or trimmed the data for the analysis part could you try these please 

• Perform Aggregations:

  • Load the transformed data into Power BI.
  • Use measures to calculate sums, averages, or other aggregations, based on your report requirements.
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

[willszee]

Akash,

Thank you for this reply. 

I need some guidance relating to how this process can be undertaken, can you assist me with this request.

Thanks you in advance.

[Akash_Varuna]

Could you please elaborate 

[willszee]

Hi Akash,

I need some guidance relating to how this type of analysis is practically undertaken, (a source of information with guidance on how to perform the required steps). 

I am trying to learn how to analysis and product an output in Power BI. When looking at the expanded list, concatenate, what would the syntax look like that would help me measure the number of rows in a column where some variables are recorded several time?

Thank you in advance.

[v-hashadapu · Accepted solution]

Hi @willszee , Thank you for reaching out to the Microsoft Community Forum.

Since expanding your lists resulted in 500 million rows, keeping the concatenated values via "Extract Values" is the best approach.

Try this:

1. If you want to count occurrences of a specific medication across all rows, try this:

Medication Count =

SUMX(

    'MainTable',

    (LEN('MainTable'[Medications]) - LEN(SUBSTITUTE('MainTable'[Medications], "MedicationName", "")))

    / LEN("MedicationName")

)

2. Or if you need a normalized structure where each medication appears in its own row, Go to Power Query, Select the Medications column -> Split Column by Delimiter. Depending on your data format, Choose a comma or semicolon. Use "Unpivot Columns" to restructure it into one row per medication. Load the transformed data back into Power BI for analysis.
3. Once your medications are structured properly, use simple DAX measures to analyze them.
4. Build Your Visuals in Power BI, add filters or slicers to analyze specific medications or patient groups.

Also, for better understanding of this, refer below documentation:

What is Power Query?

DAX overview

DAX function reference

Aggregation functions

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.