Forum Discussion
How To transform table columns into matrix
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 , 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?
