Create multiple columns from data in single column

[mm44]

Good evening

I have searched for a solution but have unable to find one.  I feel this should be a easy answer but being new to Power Bi I am unable to come up with a solution.

I have the below sample dataset

Item	Warehouse	Reorder Type	Qty
ABC	1	Standard	10
ABC	1	Customer	5
ABC	2	Standard	25
DEF	2	Standard	15
GHI	2	Sales	20

I need to return a datatable with a distinct list of Item and Warehouse codes along with columns summing the different reorder types.  Example of the expected result below

Item	Warehouse	Standard	Customer	Sales
ABC	1	10	5	0
ABC	2	25	0	0
DEF	2	15	0	0
GHI	2	0	0	20

[Seanan · Accepted solution]

Hi @mm44 

Under the Home tab in PBI Desktop click the transform data button. From here click on the Reorder tab so that it is hightlighted.

In the transform tab click pivot column and set the value to TypeQty. Click advanced options and set the aggregate value function to Don't Aggregate. 

Your table will then appear as you want it.

 

*Update*

I forgot to mention, if you wish to replace the null values with 0.

Select all three columns, right click on one of them and click replace values. Value to find = null and replace with = 0

[mm44]

I ended up going with @Seanan's suggestion as that seemed the most straight forward for a newbie like me.

I did do a test with @VH's input and it worked as well.

Thank you both for the quick responses!

[VahidDM · Accepted solution]

Hi @mm44 

You can create a new table with this DAX expression:

Table 2 = 
SUMMARIZE (
    'Table',
    'Table'[Item],
    'Table'[Warehouse],
    "Standard",
        CALCULATE ( SUM ( 'Table'[Qty] ), 'Table'[Reorder Type] = "Standard" ) + 0,
    "Customer",
        CALCULATE ( SUM ( 'Table'[Qty] ), 'Table'[Reorder Type] = "Customer" ) + 0,
    "Sales",
        CALCULATE ( SUM ( 'Table'[Qty] ), 'Table'[Reorder Type] = "Sales" ) + 0
)

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Seanan · Accepted solution]

Hi @mm44 

Under the Home tab in PBI Desktop click the transform data button. From here click on the Reorder tab so that it is hightlighted.

In the transform tab click pivot column and set the value to TypeQty. Click advanced options and set the aggregate value function to Don't Aggregate. 

Your table will then appear as you want it.

 

*Update*

I forgot to mention, if you wish to replace the null values with 0.

Select all three columns, right click on one of them and click replace values. Value to find = null and replace with = 0