Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jeralee Seaburn]

Good morning!
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/services, then across the top are programs within the services and there is a “P” in the cell if that location has that program.  I’ve attached the source data (it’s not sensitive) and a picture of the data structure.  I pulled this into power BI and created a table for the locations and one for the columns, but i can’t figure out the best way to render the “P” to indicate that ‘Yes, this location has this program’.

Please help!  I’m trying to impress my new employers but am failing!

——————————
Jeralee Seaburn
Business Intelligence Analyst
4199578326
——————————

[Jeralee Seaburn]

Additionally, after thinking about how I would prefer my data structure to be, I’ve determined this:

So, if you have any ideas on how to get the data structures within PBI this way either via the gui or dax, I would really appreciate it. 

Thanks!

——————————
Jeralee Seaburn
Business Intelligence Analyst
4199578326
——————————
——————————————-

[Vishesh Jain]

Hi ,

A little late in responding but please check if this transformation works out for you or not.

Since the rest of the values in your data are nulls, their respective rows have been eliminated.
Also, if the number of columns increase in the future, then the solution will work on that as well.

I am attaching my pbix for your reference and I hope this helps.

Thank you,

——————————
Vishesh Jain
MCSA Power BI and Excel
Emerald Award Dynamic Communities 2019
Owner
VR Construction
——————————
——————————————-

[Jeralee Seaburn]

hi Vishesh!  Thank you so much for responding.  I achieved those same results using an unpivot, but I really needed to have all of the columns and rows available for a matrix visual.  So, I did a ‘replace values’ on the nulls and just entered a blank in there.  It got me closer.

Now I’m trying to use the matrix visual to indicate which of those rows have the “P” value in them.  I would like to use an icon, but am still playing with it.  

I’m trying to get close to this, where the dots indicate a positive value (meaning that program is available in that county).  I need a similar representation for my cities.  

Do you have any advice on that?  I’ve attached my updated .pbix.

Again – thank you so much for responding!  Better late than never 🙂  

——————————
Jeralee Seaburn
Business Intelligence Analyst
4199578326
——————————
——————————————-

[Vishesh Jain]

Hi ,

Please have a look at the picture and I hope this is what you are looking for. Green dots are all those values with ‘P’.
?
I am attaching my file for you reference and if you have any questions please let me know.

Thank you,

——————————
Vishesh Jain
MCSA Power BI and Excel
Emerald Award Dynamic Communities 2019
Owner
VR Construction
——————————
——————————————-

[Jeralee Seaburn]

Yes!  This will work!  Thank you so much for your help.

——————————
Jeralee Seaburn
Business Intelligence Analyst
4199578326
——————————
——————————————-

[Vishesh Jain]

Hi ?,

If my solution worked for you, please can you mark it as the best answer, so that other with the same problem can reach the solution faster.

Thank you,

——————————
Vishesh Jain
MCSA Power BI and Excel
Emerald Award Dynamic Communities 2019
Owner
VR Construction
——————————
——————————————-