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.

GilbertQ's avatar
GilbertQ
Icon for Super User rankSuper User
4 years ago

Hi ethanlsaul 

 

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

 

  • 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.