Pivot/Unpivot Data

[ethanlsaul]

Hello,

I have tried every combo of pivot, transpose, unpivot. I want my data to go from 

Product	Plant	Qty
123	US10	100
123	US20	200
123	US30	111
456	US10	26
456	US20	33
456	US30	56
789	US10	700
789	US20	600
789	US30	555

To this:

Product	US10	US20	US30
123	100	200	111
456	22	33	56
789	700	600	555

[v-yalanwu-msft · Accepted solution]

Hi, @ethanlsaul ;

You could use pivot it.and the seting as follow:

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @ethanlsaul 

If you use a Matrix there is an option to switch values to rows

[ethanlsaul]

Hi @GilbertQ,

Thanks for replying. It worked, sort of! 

Do you know how i would go about replacing the blanks with 0's. The tricky part here is that the data isn't null for the missing column. 3rd row, 2nd line-- there isn't a line that contains anything in the raw data for that plant + item combo. I think it needs some sort of measure, but i have failed all week to figure it out. I wrote it here: Missing Data in inputs resulting in blanks on Matr... - Microsoft Power BI Community 

[GilbertQ]

Hi @ethanlsaul 

What you could do is to create a measure and where it finds a blank to replace it with zero as shown with the example below

Qty (With Zeros) = IF(ISBLANK('Table Name'[Qty]),0,SUM('Table Name'[Qty]))

[ethanlsaul]

Not exactly-- it seems like in the field after isblank it needs another measure, which i dont have.