Forum Discussion
How To transform table columns into matrix
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
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
4 Replies
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
Hi mahoneypat thanks for your solution.. it does give me the solution .. great
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.
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?
