Reply
Frequent Visitor
Posts: 4
Registered: ‎07-22-2018
Accepted Solution

unpivot and append multiple columns

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  
CityBranche Monthly ChargesxxxxxxMonthly ChargesOne Time ChargexxxMonthly ChargesOne Time Charge
New York1500  50250 90260
New York2600  60260 90280
New York3500  50280 90260
Boston1600  50260 50270
Boston2500  50270 80250
Boston3600  50250 50230
Chicago1500  40230 80250
Chicago2600  60250 80250
Chicago3600  50250 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 


Accepted Solutions
Community Support Team
Posts: 4,051
Registered: ‎07-09-2016

Re: unpivot and append multiple columns

@John_C_,

 

You may take a look at the post below.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Community Support Team
Posts: 4,051
Registered: ‎07-09-2016

Re: unpivot and append multiple columns

@John_C_,

 

You may take a look at the post below.

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

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor
Posts: 4
Registered: ‎07-22-2018

Re: unpivot and append multiple columns

Hi Sam, 

 

awesome! You saved my day! Thanks a lot! 

 

John