Convert String from Database Entries into Matrix Visual

[GG3Jables]

Hi! 

I am pretty new to Power BI and need some help for an evaluation I want to to, for a problem I am struggling with at the moment. 

Basically I have a big database, in which type of same strings are collected in several columns like underneath: 

ID	Rack1	Rack2	Rack3
1	Coke; Sprite	0	0
2	0	Pepsi	0
3	Pepsi; Coke	Sprite	Coke

All I want to do is to count the single strings and collect them in a matrix visual like here: 

Type	Rack1	Rack2	Rack3
Coke	2	0	1
Sprite	1	1	0
Pepsi	1	1	0

Can someone please explain if and how it is possible to convert the data into a matrix visual? 

Greets ! 

[Anonymous]

Hi, @GG3Jables 

May I ask if you have gotten this issue resolved? If it is solved, please share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

If it is not resolved, I hope you will provide the full .pbix file via OneDrive or SharePoint. Please be careful to remove all sensitive information and we will do our best to provide ideas for your issue.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GG3Jables]

Hi, @Anonymous

I am still wortking on the unpivot solution of @danextian. 

That will take some time because I am still a beginner in DAX and Power BI, but I will keep this updated! 

Unfortunately due to company restrictions I cant share the database, but I will trry to crate a smaller but similiar database and add it to the post

[GG3Jables]

@danextian : Thank you very much for the reply!

I tried it on a small sample database and it worked exactly like you described, but when used in my big database the distinct count function doesn´t work properly anymore. 

But nevertheless thank you very much ! 

[danextian]

Sorry, is it the distinct count function that didn't work (very unlikely) or you couldn't unpivot due to sheer size of the data?

[GG3Jables]

So the problem is that when I unpivot the database, the rows without an entry are not reconsidered. 

Unfortunately I can´t upload a file directly and can´t upload data in google drive or sharepoint due to company IT restrictions. 

Before unpivot the measure for ID count is 2745, afterwards its 210.

How do I have to do the unpivot to get those? 

[GG3Jables]

Update: Found the problem, because the fields were filled with "null" they were not reconsidered. 

After exchange with "0" it worked just like @danextian said before :). 

@danextian : Again, thank you very much ! 

[danextian · Accepted solution]

Hi @GG3Jables 

In the query editor, you can unpivot all other columns than ID, split the resulting value column into rows and do some cleaning before loading then into the model.  And here's the result:

Note: Ideally transformations should be done at the source and since you mentioned that it is in a big database, expect for slow loading/data refresh.

Sample pbix attached.