Log In
Register
Home
September 15, 2020
Offline
Do have some insight or advice you can give me on how to transform this messy non adjacent data into a useable row and column structure?
I believe Power Query is made for this type of transformation, however, I am not sure of the best or standard approach using interface or M code after doing some testing and searching
This is one of 500 blocks, so I want to repeat after I build the query.
I could use Excel and VBA which I know exactly what steps and approach, although it seems PQ was designed to do this clean up and restructure.
I am thinking I should index or group each block somehow as a starting point, but this is really new territory
I did some fairly involved transformations before in my corporate life, but data set was simpler.
See Answer
July 16, 2010
Offline
Hi Robert,
You haven't provided an example of how you would like this data to look once it has been transformed, but assuming the data in the first 31 rows needs adding to columns in rows 32 to 43 then I would do this in two parts by referencing the data in two queries, one for the first 31 rows and one for the remainder. Then you can fix the first 31 rows and then join it to the data in rows 32 to 43.
Something that looks odd is that the data in the first 31 rows doesn't contain any actual information e.g Work Order Entered Date appears to contain the number 10, which obviously isn't a date. And looking at all of the data in the top half each field only contains a number, so you could possibly just remove the top section of data because it looks useless.
If that's the case, you can use Power Query to Remove the Top 29 rows, then remove the empty columns and you're done.
Mynda
September 15, 2020
Offline
Thanks, I will follow up with end state example to make sure I am clearer.
In meantime, I will do suggested steps.
Regards,
Robb
September 15, 2020
Offline
Hi Mynda:
Please see complete tab, this is a better, more complete example.
Its pretty messy and mixed, but anything will help. I need to repeat for 500 other groups, too, but custom function is for another day.
Robb
July 16, 2010
Offline
Hi Robb,
There's no easy way to do this. In the attached file I've created 4 queries for the header data, one for each set, then appended them together into one table (headers).
The transaction data is then loaded in a separate table and you can either merge or append this to the headers. In the attached file I've done both.
Hope that helps.
Mynda
Answers Post
1 Guest(s)
