Moving columns 3 and 4 under column 1 and 2 (then do the same for 5 and 6)

[AlienRullz]

Hi, 

Should probably start by saying that I'm new to Power BI and any help is greatly appreciated. 

I'm faced with the following issue: My table contains 6 columns 

Room1	Data1	Room2	Data2	Room 3	Data 3
Lounge	293	Bedroom	394	Office	233
Lounge	149	Bedroom	365	Office	390

What I want to achieve is

Room	Data
Lounge	293
Lounge	149
Bedroom	394
Bedroom	365
Office	233
Office	390

I tried un-pivoting the rooms only but what ends up happening is my data will get duplicated in a weird way. 

I feel like there's an obvious solution to this, I just can't figure it out. 

Thanks in advance for your help! 

[AlienRullz]

Thanks to you both!!! Problem solved!! 

[mahoneypat]

I recently made a video that shows a couple approaches for just this scenario.  Please check it out.  See the example on Repeating Columns.

(2) Faster Data Transformations with List/Record M Functions - YouTube

Pat

[stevedep · Accepted solution]

Hi,

Please see below:

let
    
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8skvzUtPVdJRMrI0BpJOqSlF+fm5QJaxpQmQ9E9Ly0wGSxsbK8XqIKk3NLFEVW9miqze2NJAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Room1 = _t, Data1 = _t, Room2 = _t, Data2 = _t, #"Room 3" = _t, #"Data 3" = _t]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Room 3", "Room3"}, {"Data 3", "Data3"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Room1", type text}, {"Data1", Int64.Type}, {"Room2", type text}, {"Data2", Int64.Type}, {"Room3", type text}, {"Data3", Int64.Type}}),
    Part1 = Table.SelectColumns(#"Changed Type", {"Room1", "Data1"}),
    #"Renamed Columns1" = Table.RenameColumns(Part1,{{"Room1", "Room"}, {"Data1", "Data"}}),
     Part2 = Table.SelectColumns(#"Changed Type", {"Room2", "Data2"}),
    #"Renamed Columns2" = Table.RenameColumns(Part2,{{"Room2", "Room"}, {"Data2", "Data"}}),
     Part3 = Table.SelectColumns(#"Changed Type", {"Room3", "Data3"}),
    #"Renamed Columns3" = Table.RenameColumns(Part3,{{"Room3", "Room"}, {"Data3", "Data"}}),
    Final = Table.Combine({#"Renamed Columns1", #"Renamed Columns2", #"Renamed Columns3"})

in
  Final