Transforming column of csv's into a boolean matrix

[Anonymous]

I need help with a transformation that I haven't been able to figure out and couldn't find referenced elsewhere.  I am reading in two columns of data: uniqueIDs and a comma-delimited list for each uniqueID. Each uniqueID will have only distinct values in its comma-delimited list, but those values may or may not show up across the lists for different uniqueIDs. I want to transform this into a matrix where the rows are still each of the uniqueIDs but the columns are each unique entry in the comma-delimited lists, such that the matrix populates in some boolean fashion to indicate true or false whether a column value was in the given uniqueID's list. How can I accomplish this in my query transformaiton?

I am doing this as an intermediate step so I can calculate counts (what % of all unique IDs contain a given value, for a given uniqueID what % of all unique values does its list contain, etc.), so if there is a better way to do this that generating a true/false table that could be helpful as well.

Thanks for the support!

Sample data input and desired output in the attached xlsx (is there any way to directly attach a file here?)

https://filebin.net/a49psoqqphfpb9tt

[MFelix]

Hi @Anonymous ,

On the query editor select split colum by delimeter, and then on the advance options select rows.

The you will get a table similar to this:

id      fruits

1	apple
1	orange
1	durian
1	elderberry
2	apple
2	banana
2	orange
2	durian
2	elderberry
3	orange
3	durian
3	elderberry
4	elderberry
5	banana
5	durian
5	elderberry
6	apple
6	banana
6	orange
6	durian
6	elderberry
7	apple
7	elderberry
8	apple
8	orange
8	durian
8	elderberry
9	durian
9	elderberry
10	apple
10	banana
10	orange
10	durian
10	elderberry

Create a table with the fruits and make a relationship with the previous table.

Add the measure:

Fruits Count = COUNT('Table'[id])+0

Now create a matrix with the following setup:

• Rows: ID
• Columns: Fruits
• Values: Fruits Count.

Check PBIX file attach.

[Anonymous]

Thanks for the reply @MFelix . I'd like this not just to be a matrix visualization, but a newly created Table itself in my PowerBI data that I can calculate on in the future. So I am trying to figure out how I can generate such a table in exactly the way you made the matrix visualization. Is this possible?

[MFelix · Accepted solution]

Hi @Anonymous ,

You can add the following steps after steps to be made after the split by delimeter or create a new table referencing the first one.

• Create a custom column with value 1
• Choose that Column fruits
  • Summarize by custom column - Count All

let
    Source = Table,
    #"Added Custom" = Table.AddColumn(Source, "Custom", each 1),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[fruits]), "fruits", "Custom", List.Count)
in
    #"Pivoted Column"

Check PBIX file attach.

[Anonymous]

This is perfect, need to be more flexible in how I think about pivoting. Thank you!

[MFelix]

Hi @Anonymous ,

Please don't forget to mark the correct answer so it can help others.