columns in table to become rows merging queries

[ninakarsa]

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 quantity, qty 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

[ninakarsa]

thank you so much for your help

[JustJan]

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