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

 

How can I achieve that?

 

Thanks in advance

 

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!

     

     

6 Replies

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

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykosUNJRMjbUt9A3MjAyArINIXxzGN9EKVYnWqm0OBFNnRGaOlOwutTcVHSFxmgKzZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [region = _t, month1 = _t, item1 = _t, month2 = _t, item2 = _t]),
    #"Split to columns" = Table.ToColumns(Source),
    #"Combined tables" = let hdr = #"Split to columns"{0} in Table.Combine(List.Transform(List.Split(List.Skip(#"Split to columns"),2), each Table.FromColumns({hdr} & _)))
in
    #"Combined tables"