cancel
Showing results 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

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

Announcements

#### 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 Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors