Forum Discussion

GabrielDC's avatar
GabrielDC
Frequent Visitor
3 years ago
Solved

How do I transform this table?

HI All Sorry if this is a rookie question. I have this table [above] with severals months and their items. I'd like to put all the months together in one single column [like the table below].   H...
Need Help
  • GabrielDC's avatar
    GabrielDC
    3 years ago

    Thanks Greg, your answer inspired me to try something:

     

    1) convert all fields to text type

    2) concatenate each month with their respective item

     

    3) Unpivot by these concatenated columns

    4) delete the month/items columns

    5) Split the concatenated column

    6) Profit 🙂

     

    Thanks Greg, we did this!

     

     

  • GabrielDC's avatar
    GabrielDC
    Frequent Visitor
    3 years ago

    Thanks Greg, the thing is I have 12 months columns in the Table so that's going to be a lot of Tables and Appends in the Editor (which is messy already 😞 ),  is there anything that can be done with Unpivot or Transpose?

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      GabrielDC Understood but that's quite possibly the worst formatting for an Excel spreadsheet to ingest into Power BI possible. If you had this:

      Combined 7/31/2022 8/31/2022

      usa

      		 5 2
      japan 10 3
      emea 6 5
           

      Then, yes, you could select "combined" column, right-click and select "Unpivot other columns". But, you don't have that.

      • GabrielDC's avatar
        GabrielDC
        Frequent Visitor
        3 years ago

        Thanks Greg, your answer inspired me to try something:

         

        1) convert all fields to text type

        2) concatenate each month with their respective item

         

        3) Unpivot by these concatenated columns

        4) delete the month/items columns

        5) Split the concatenated column

        6) Profit 🙂

         

        Thanks Greg, we did this!