Forum Discussion
Convert String from Database Entries into Matrix Visual
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 !
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.
7 Replies
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.
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?
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?
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.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
