Forum Discussion
Solved
Create column above columns
Hello everybody, is there a way to do this?? in the source table columns are: pieni_export,cmp_export,gp_export,vid_export,pieni_import,cmp_import,gp_import,vid_import i want to sho...
We need to do some transforms to you source table in Query Editor firstly. Assuming we have a Table1 like below.
- Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
- Rename Attribute to SubType.
- Select SubType and click Split Column by Delimiter in Home ribbon.
- Rename SubType.1 to SubType, SubType.2 to Type.
- Right click Table1 in left Queries pane and select Duplicate, rename the duplicated table to Table2.
- Remove the Type column in Table2 and add another new custom column named Type with “TOTAL” string.
- Click Group By in the Home ribbon, sum the values group by Type and SubType.
- Select Table1 in left Queries pane and click Append Queries in Home ribbon. Select Table2 to append.
- Close & Apply Query Editor. Drag Type and SubType into Columns, Value into Values in Matrix.
I’ve also uploaded my PBIX file here for reference.
Best Regards,
Herbert
- Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
We need to do some transforms to you source table in Query Editor firstly. Assuming we have a Table1 like below.
- Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
- Rename Attribute to SubType.
- Select SubType and click Split Column by Delimiter in Home ribbon.
- Rename SubType.1 to SubType, SubType.2 to Type.
- Right click Table1 in left Queries pane and select Duplicate, rename the duplicated table to Table2.
- Remove the Type column in Table2 and add another new custom column named Type with “TOTAL” string.
- Click Group By in the Home ribbon, sum the values group by Type and SubType.
- Select Table1 in left Queries pane and click Append Queries in Home ribbon. Select Table2 to append.
- Close & Apply Query Editor. Drag Type and SubType into Columns, Value into Values in Matrix.
I’ve also uploaded my PBIX file here for reference.
Best Regards,
Herbert
very Very nice solution
