Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am recently back to work for a new company after a few month layoff and my first little project has me somewhat stumped. It feels like I should know how to do this, but if any of you can guide me to tutorials, videos, instructions, I would be forever grateful.
I have a matrix-style excel sheet that lists states/divisions/programs, then across the top are EBP services within the programs and there is an indicator in the cell if that location has that program. It will be "P" for Planned or "I" for Implemented.
Please help! I'm trying to impress my new employers but am failing!
Here is a screenshot of the source data:
I tried to flatten this in PowerBI by creating a table for StateDivisionProgram and then a table for the EBP columns and then CROSSJOIN them to create a cartesion table. I thought about creating a column named "Status" and then entering the word "Planned" in the record where it would be indicated, but this seems like too much manual work to maintain.
Please let me know if there is more info I can provide (how to attach files to this thread?) or if anyone can help me figure this out. I tried the PBIUG (Dynamic Communities) forum, but haven't received any responses.
Thank you!
Hi @jeralee2 ,
What's the expected result? I didn't see any program connect to the services in that table.
Please show more details.
Best Regards,
Jay
Hi Jay!
Thank you for the response. I'm heading towards a solution similar to this tableau report. I'll be using states and divisions rather than counties. The dots on the right are simply to indicate that the location has that particular program. There is nothing notable about the colors.
Hi! Thank you for offering to help! I'm not sure I completely understand. How is the best way for me to share the dummy data table with you? I don't see a way to attach a file to this thread.
normally people share them via dropbox, its the best way to post a dropbox link here for the file,
basically theres a functions on power query that let you dinymically convert mitrix type data into a tabulated table by converting certain columns or rows to row of column matchin the other matrix axis.
something like this:
Proud to be a Super User!
Hi! Yes...I think this is exactly what I need. My organization doesn't allow dropbox, but I'm working to try and share a file from my OneDrive that contains the data I need converted. Thank you!
copy the data, and change the data with dummy data, share only the structure with dummy invented data on the actual values, information etc.
Proud to be a Super User!
WIll this link work?
If this doesn't allow you to see the file, would you just be able to share with me the function or M code that is in PowerQuery that can make this conversion? If you just point me that way, I may be able to find it.
Thank you!
hey,
basically to get this type of excel matrix into a nice table you will need to do some news tables let alone the matrix axis (mergenti the 3 first column into 1 with interscaleted coma for starting) and them do some unpivoting to get the right mix, if you share a dummy data table file could share with you a M code for that dummy data on how to do it.
Proud to be a Super User!
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |