cancel
Showing results for
Did you mean:
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

2 REPLIES 2
Helper II

thank you so much for your help

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