unpivot and append multiple columns

[John_C_]

Hi everybody, 

hope, this is an interessting task for you. I got a bit stuck and hope, you can help me. 

I have a long table with many columns like this: 

		Access Line 1			Switch 1			Router
City	Branche	Monthly Charges	xxx	xxx	Monthly Charges	One Time Charge	xxx	Monthly Charges	One Time Charge
New York	1	500			50	250		90	260
New York	2	600			60	260		90	280
New York	3	500			50	280		90	260
Boston	1	600			50	260		50	270
Boston	2	500			50	270		80	250
Boston	3	600			50	250		50	230
Chicago	1	500			40	230		80	250
Chicago	2	600			60	250		80	250
Chicago	3	600			50	250		50	300

In reality I have more than 20 objects like Access Line 1, 2, 3, 4, ... Switch 1, 2, 3, ... etc ... 

1. I would like to put the 1. Header "Access Line 1", "Switch", etc. into another column called "Object" 

2. I would like to put the "Monthly Charges" and "One Time Charges" into the column called "Charge" 

2. I would like to put the values into an column called "Value". 

My goal would be like this: 

City	Branche	Object	Charge	Value
New York	1	Access Line 1	Monthly Charges	500
New York	2	Access Line 1	Monthly Charges	600
New York	3	Access Line 1	Monthly Charges	500
Boston	1	Access Line 1	Monthly Charges	600
Boston	2	Access Line 1	Monthly Charges	500
Boston	3	Access Line 1	Monthly Charges	600
Chicago	1	Access Line 1	Monthly Charges	500
Chicago	2	Access Line 1	Monthly Charges	600
Chicago	3	Access Line 1	Monthly Charges	600
New York	1	Switch 1	Monthly Charges	50
New York	2	Switch 1	Monthly Charges	60
New York	3	Switch 1	Monthly Charges	50
Boston	1	Switch 1	Monthly Charges	50
Boston	2	Switch 1	Monthly Charges	50
Boston	3	Switch 1	Monthly Charges	50
Chicago	1	Switch 1	Monthly Charges	40
Chicago	2	Switch 1	Monthly Charges	60
Chicago	3	Switch 1	Monthly Charges	50
New York	1	Switch 1	One Time Charge	250
New York	2	Switch 1	One Time Charge	260
New York	3	Switch 1	One Time Charge	280
Boston	1	Switch 1	One Time Charge	260
Boston	2	Switch 1	One Time Charge	270
Boston	3	Switch 1	One Time Charge	250
Chicago	1	Switch 1	One Time Charge	230
Chicago	2	Switch 1	One Time Charge	250
Chicago	3	Switch 1	One Time Charge	250

I could unpivot and append all the 30 columns manually. But is there a better solution like a for ... next - loop? 

Thanks a lot in advanced

John 

[v-chuncz-msft · Accepted solution]

@John_C_,

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Merged-Cells-in-Source/td-p/194473

[John_C_]

Hi Sam, 

awesome! You saved my day! Thanks a lot! 

John