Create Table from Query for a Matrix/Graph

[Anonymous]

Hi all, 

I have data in Power BI Desktop connected to a Dynamics app through the XrmToolBox tool "Power Query (M) Builder". This data has Results and Targets for various performance metrics by quarter and year. I would like to create a table that transposes the Results and Targets to columns. That way I can create a "percent to target" for each performance metric at each quarter. 

This is what my data currently looks like. I created a Unique ID concatening the performance metric and time:

Unique ID	Results	Target
Project investment:2019 Q2		225000
Project investment:2019 Q2	215000
Quality of Health Rating:2019 Q1		1
Quality of Health Rating:2019 Q1	1

What I would like the data to show:

Unique ID	Results	Target
Project investment:2019 Q1	215000	225000
Quality of Health Rating:2019 Q1	1	1

I have experience making Power BI Dashboards and Tableau dashboards look very pretty. But this is my first time really having to alter the data. 

The "raw" data cannot be merged the way I would like these three columns to be. It contains other unique values that need to be separated for results and targets. Is there a way to create a new measure that does this? Or create an intermediary table?

Thank you all!

[v-xuding-msft]

Hi @Anonymous ，

After testing, I think it is the best way to implement it in Power Query as FrankAT suggested. 

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This was really helpful. Thank you!!!

[FrankAT · Accepted solution]

Hi,

with your sample data you can use Power Query to get the desired result:

1. Open Power Query Editor and select column Unique ID --> Unpivot other columns
2. Select column Attribut --> Pivot Column (Value column is Value).

Done

Regards FrankAT