Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jeralee2
Frequent Visitor

Connect to Matrix-style excel sheet in Power BI Desktop

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:

 

SourceDataScreenShot.PNG

 

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! 

 

8 REPLIES 8
v-jayw-msft
Community Support
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

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. 

 

TableauScreenShot.png

jeralee2
Frequent Visitor

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: 

StefanoGrimaldi_0-1611269502477.png

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




WIll this link work?

 

https://safy365-my.sharepoint.com/:x:/g/personal/seaburnj_safy_org/EcRz8EbubddCghESheGbqKIB2vtfhAwvD...

 

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!

StefanoGrimaldi
Resident Rockstar
Resident Rockstar

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.