Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Iheb_Ghali
Frequent Visitor

Create column above columns

Hello everybody,

is there a way to do this??

 

sss.png

 

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 Smiley Sad 

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

@Iheb_Ghali

 

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

Create column above columns_1.jpg

 

  1. Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
    Create column above columns_2.jpg
  2. Rename Attribute to SubType.
  3. Select SubType and click Split Column by Delimiter in Home ribbon.
    Create column above columns_3.jpg
  4. Rename SubType.1 to SubType, SubType.2 to Type.
    Create column above columns_4.jpg
  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.
    Create column above columns_5.jpg
  7. Click Group By in the Home ribbon, sum the values group by Type and SubType.
    Create column above columns_6.jpg
  8. Select Table1 in left Queries pane and click Append Queries in Home ribbon. Select Table2 to append.
    Create column above columns_7.jpg
  9. Close & Apply Query Editor. Drag Type and SubType into Columns, Value into Values in Matrix.
    Create column above columns_8.jpg

I’ve also uploaded my PBIX file here for reference.

 

Best Regards,

Herbert

View solution in original post

4 REPLIES 4
v-haibl-msft
Employee
Employee

@Iheb_Ghali

 

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

Create column above columns_1.jpg

 

  1. Select all columns in Table1 and click Unpivot Columns in Transform ribbon.
    Create column above columns_2.jpg
  2. Rename Attribute to SubType.
  3. Select SubType and click Split Column by Delimiter in Home ribbon.
    Create column above columns_3.jpg
  4. Rename SubType.1 to SubType, SubType.2 to Type.
    Create column above columns_4.jpg
  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.
    Create column above columns_5.jpg
  7. Click Group By in the Home ribbon, sum the values group by Type and SubType.
    Create column above columns_6.jpg
  8. Select Table1 in left Queries pane and click Append Queries in Home ribbon. Select Table2 to append.
    Create column above columns_7.jpg
  9. Close & Apply Query Editor. Drag Type and SubType into Columns, Value into Values in Matrix.
    Create column above columns_8.jpg

I’ve also uploaded my PBIX file here for reference.

 

Best Regards,

Herbert

Thank you very much @v-haibl-msft !!!

 

You saved my life, its working like magic Smiley Happy

very Very nice solution

HeroPost
Frequent Visitor

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

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors