Forum Discussion
Create multiple columns from data in single column
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 |
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
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
3 Replies
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
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
