Showing results for 
Search instead for 
Did you mean: 
Helper II
Helper II

columns in table to become rows merging queries

Hi members

I am fairly new to powerbi and I apprecaite any help.


I have table A, showing the following information, which shows by product the total quantity available, which is then split by area.

Table A

Product, Total quantity, Area A,  AreaB, AreaC 

ABC,          100,                     20,          30,        50 

CDA,          50,                       0,           20,         30

CDB,           25,                      15,          5,           5


Table B:

Product, qty sold, Area

ABC,        5,         Area A

ABC,        0,         Area B

ABC,        30,      Area C

CDA,        15,      Area B

CDB,         10,      Area A

CDB,         3,      Area B

CDB,         5,      Area C


I want to be able to calculate the % of products sold by product and area. How can I turn table B as below in order to create these calculations?


Table C:

Product,   Area, Total quantityqty sold

ABC,         Area A, 20, 5

ABC,         Area B, 20,0

ABC,        Area C, 50,30

CDA,        Area A, 0,0

CDA,        Area B, 20, 15

CDA,        Area C, 30,0

CDB,       Area A, 15, 10

CDB,      Area B, 5, 3

CDB,     Area C, 5, 5

Helper II
Helper II

thank you so much for your help

Responsive Resident
Responsive Resident

Hi @ninakarsa , 


If you unpivot Table A. Rename the columns as you want. Attribute to 'Area' and Value to 'Area Total QTY'

you can then do a Merge Queries as new with the Product and Area columns as keys between the two tables. 


This will result in a Table C with the columns Product, Total quantity, Area, Area Total QTY and a column called 'Table B'


Then you only have to  expand the column (click on the symbol with the left and right arrows) and only select the column 'qty sold' and you you the table you requested.


hope this helps


Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors