Is it Possible to Merge 2 Columns into 1 (by creating new rows)

[Cottswald]

I have reports automatically emailed to me from my company server. I currently do transformation in Excel, but am looking to get the process setup in PowerBI for automation.

My problem is that the reports are sent in this format, and I cannot change them:

Name

Type 1

Type 2

Type 3

Type 4

Type 5

I would like to use a query in BI to transform the data to this:

Name	Type 1
Name	Type 2
Name	Type 3
Name	Type 4
Name	Type 5

I have not been able to find a soultion anywhere to this. Pivoting & un-pivoting columns doesn't do what I am trying to do.
I am trying to merge all my 'Type' columns into a single column, essentially adding it as additional rows to an exisitng column.
Is this even possible in PowerBI? Any suggestions would be greatly appreciated!

[v-yiruan-msft · Accepted solution]

Hi @Cottswald ,

I created a sample pbix file(see the attachment), please check if that is what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RcqhDcNAEETRXg4b5GZnbFfhBqwDAYFh6V+Rviwteejd97je389rjm38JgoLjZljbU8UEYWFxqhjEVFYaEx1NBGFhca4Y4goLDQmHXciCguN2TseRBQWGnN0PIkoLDTmHGv9AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Type 1" = _t, #"Type 2" = _t, #"Type 3" = _t, #"Type 4" = _t, #"Type 5" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Type 1", type text}, {"Type 2", type text}, {"Type 3", type text}, {"Type 4", type text}, {"Type 5", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Name"}, "Types", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"Name", "Types"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Best Regards

[FreemanZ]

hi @Cottswald 

try to paste some dummy but concrete data?