How do I transform this table?

[GabrielDC]

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

[CNENFRNL]

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"

[Greg_Deckler]

@GabrielDC See attached PBIX below sig.

[GabrielDC]

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]

@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 · Accepted solution]

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!

[Greg_Deckler]

@GabrielDC Very nice!