cancel
Showing results for 
Search instead for 
Did you mean: 
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

 

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. 

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
Solution Sage
Solution Sage

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.