topic Re: Pivot/Unpivot Data in Service

Pivot/Unpivot Data

ethanlsaul — Thu, 19 May 2022 21:14:34 GMT

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

Re: Pivot/Unpivot Data

GilbertQ — Thu, 19 May 2022 22:33:55 GMT

Hi ethanlsaul

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

Re: Pivot/Unpivot Data

ethanlsaul — Fri, 20 May 2022 20:12:44 GMT

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

Re: Pivot/Unpivot Data

GilbertQ — Sun, 22 May 2022 22:29:44 GMT

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]))

Re: Pivot/Unpivot Data

ethanlsaul — Mon, 23 May 2022 12:12:49 GMT

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

Re: Pivot/Unpivot Data

v-yalanwu-msft — Tue, 24 May 2022 07:24:50 GMT

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.