CODEWITHSUNDEEP
powerbidax
quadroid
quadroid

Reputation: 8950

Flatten Column of Lists

How can I flatten a column that contains a list in every cell using DAX in Power BI? Every record in my DataSource contains a list of codes, I want to combine all records and count the occurrences. Can this be done in PowerBI using the DAX Measures?

The Rows in my PowerBI Query look like this:

╔═══════════╦══════╗
║ Timestamp ║ Code ║
╠═══════════╬══════╣
║ 08:00     ║ List ║
║ 09:00     ║ List ║
╚═══════════╩══════╝

The List itself is just string (here is the Json from the DocumentDB)#

"Codes": [
  "036.005",
  "038.008",
  "038.009",
  "060.129",
  "060.12F",
  "060.224"
]

Upvotes: 1

Views: 2433

Answers (1)

Mike Honey
Mike Honey

Reputation: 15037

I would address this in the Query Editor. If your column is a List datatype, then just to the right of the column name you will see the Expand button. It looks like two arrows, bent left and right.

The following article shows what that Expand button looks like and describes the process for expanding a table-type column. Expanding a list-type column is similar, but will result in a single new column (replacing the list-type column), with a row generated for each entry in the list.

https://support.office.com/en-us/article/Expand-a-column-containing-a-related-table-Power-Query-d5e552be-c143-4f06-9a5e-0960bbaaf480

You could then use a Query Editor Group By with a Count or use the DAX COUNTROWS function on the result to get what you want. If the contents of the list have some analytic or reporting merit, then I would go with DAX COUNTROWS.

Upvotes: 2

Related Questions