How To transform table columns into matrix

[raviqul]

Hi Power BI Experts,

I'm trying to create a matrix like a picture below 

From a table with format like this : 

well i already try to utilize pivot-unpivot column function and such.. but still confused how to properly use it

is there anyway to solve it ?

Thanks 

[mahoneypat · Accepted solution]

You can do this in the query editor.  Since you shared an image of your table and not something easily pasted, specific M code isn't given.  However, these are the steps:

1. Select your project column, right click and choose "unpivot other columns"

2. Select the new Attribute column and in the ribbon choose split by and enter a space (and do it only at the first delimiter)

3. Rename your columns to what you want

4. Use the column that has Revenue, Cost, Net Income on rows in a matrix visual.  Put the column with Actual To Date, etc. in for columns, and put the Value column in the Values area with a Sum aggregation

Regards,

Pat

[raviqul]

Hi @mahoneypat thanks for your solution.. it does give me the solution .. great 

[amitchandak]

@raviqul , This like split of measure into measure and dimesnion.

In matrix you have an option "Show on Row", but that will not help.

You have create a table with union and summarize

example

union(
summarize("Measure","Share of Voice", "YTD",[Share of Voice YTD], "LYTD",[Share of Voice LYTD], "Change %",[Share of Voice %]),
summarize("Measure","SOV Rank", "YTD",[SOV Rank YTD], "LYTD",[SOV Rank LYTD], "Change %",[SOV Rank %])
)

Or

union(
summarize('Table',"Measure","Min","Test1",min('Table'[Test1]),"Test2",min('Table'[Test2]),"Test3",min('Table'[Test3]))
summarize('Table',"Measure","Max","Test1",max('Table'[Test1]),"Test2",max('Table'[Test2]),"Test3",max('Table'[Test3]))
)

Add the group by if you need filter in new table, Use this one for display.

[raviqul]

hello @amitchandak  thanks for the respond.. I already tried your solution and the result table is like picture below 

why the columns shown like that? is it because union that cost column merge into revenue?