Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
You may take a look at the post below.
https://community.powerbi.com/t5/Desktop/Merged-Cells-in-Source/td-p/194473
You may take a look at the post below.
https://community.powerbi.com/t5/Desktop/Merged-Cells-in-Source/td-p/194473
Hi Sam,
awesome! You saved my day! Thanks a lot!
John
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |