Forum Discussion

Iheb_Ghali's avatar
Iheb_Ghali
Frequent Visitor
9 years ago
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 show them like the way above using matrix :smileysad: 

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    Iheb_Ghali

     

    We need to do some transforms to you source table in Query Editor firstly. Assuming we have a Table1 like below.

     

    1. Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
    2. Rename Attribute to SubType.
    3. Select SubType and click Split Column by Delimiter in Home ribbon.
    4. Rename SubType.1 to SubType, SubType.2 to Type.
    5. Right click Table1 in left Queries pane and select Duplicate, rename the duplicated table to Table2.
    6. Remove the Type column in Table2 and add another new custom column named Type with “TOTAL” string.
    7. Click Group By in the Home ribbon, sum the values group by Type and SubType.
    8. Select Table1 in left Queries pane and click Append Queries in Home ribbon. Select Table2 to append.
    9. 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

4 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    Iheb_Ghali

     

    We need to do some transforms to you source table in Query Editor firstly. Assuming we have a Table1 like below.

     

    1. Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
    2. Rename Attribute to SubType.
    3. Select SubType and click Split Column by Delimiter in Home ribbon.
    4. Rename SubType.1 to SubType, SubType.2 to Type.
    5. Right click Table1 in left Queries pane and select Duplicate, rename the duplicated table to Table2.
    6. Remove the Type column in Table2 and add another new custom column named Type with “TOTAL” string.
    7. Click Group By in the Home ribbon, sum the values group by Type and SubType.
    8. Select Table1 in left Queries pane and click Append Queries in Home ribbon. Select Table2 to append.
    9. 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

  • HeroPost's avatar
    HeroPost
    Advocate I
    9 years ago

    Hi,

    it can be done, but you may have to edit your source data, so the export, and import ar dimensions in one column and Pieni, CMP, GP and vid are in another column

    then you can use the dimensions as columns in a matrix visual