tazeo
Board Regular
- Joined
- Feb 15, 2007
- Messages
- 133
- Office Version
- 365
- Platform
- Windows
Hi everyone,
I'm struggling with transforming a complex Hierarchical Table into a format suitable for creating a PivotTable. The existing data structure is proving challenging to work with.
Current Data Structure (example):
The dataset contains approximately 1,000 rows. The "Null (x)" entries appear in columns Level 05 through Level 09. When a column contains "Null (x)", it should inherit the value from the previous non-null column to its left.
Desired Output:
I can clean this manually, but it's too time-consuming for the volume of data I'm working with. I believe there must be a way to automate this process, as it's a repetitive task.
Questions:
I've been grappling with this for a while, so I apologize if my explanation isn't entirely clear. I'm open to providing any necessary clarifications. Thanks in advance for your help!
I'm struggling with transforming a complex Hierarchical Table into a format suitable for creating a PivotTable. The existing data structure is proving challenging to work with.
Current Data Structure (example):
| Level 04 | level 05 | level 06 | level 07 | level 08 | level 09 | Unit | Name | Position | Count |
| CEO | |||||||||
| Corporate Affairs | |||||||||
| Brand | |||||||||
| Digital Content | |||||||||
| Null (1) | |||||||||
| Null (1) | |||||||||
| Brand | |||||||||
| Bob | Manager | 1 | |||||||
| Null (1) |
The dataset contains approximately 1,000 rows. The "Null (x)" entries appear in columns Level 05 through Level 09. When a column contains "Null (x)", it should inherit the value from the previous non-null column to its left.
Desired Output:
| Level 04 | Level 05 | Level 06 | Level 07 | Level 08 | Level 09 | Unit | Name | Position | Count |
| CEO | Corporate Affairs | Brand | Digital Content | Digital Content | Digital Content | Brand | Bob | Manager | 1 |
| CEO | Corporate Affairs | Brand | ... | ... | ... |
I can clean this manually, but it's too time-consuming for the volume of data I'm working with. I believe there must be a way to automate this process, as it's a repetitive task.
Questions:
- Is there an efficient way to handle the "Null (x)" entries? This is where I'm getting stuck.
- Would Power Query be an appropriate tool for this task?
- Are there any other approaches or tools you'd recommend?
I've been grappling with this for a while, so I apologize if my explanation isn't entirely clear. I'm open to providing any necessary clarifications. Thanks in advance for your help!
