Forum Discussion

ethanlsaul's avatar
ethanlsaul
Icon for Helper I rankHelper I
4 years ago
Solved

Pivot/Unpivot Data

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 ...
Need Help
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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.

ethanlsaul's avatar
ethanlsaul
Icon for Helper I rankHelper I
4 years ago

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's avatar
GilbertQ
Icon for Super User rankSuper User
4 years ago

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's avatar
    ethanlsaul
    Icon for Helper I rankHelper I
    4 years ago

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