Forum

Power Query M messy...
 
Notifications
Clear all

Power Query M messy noisy data file non adjacent data

6 Posts
2 Users
0 Reactions
187 Views
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 

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.

 
Posted : 27/10/2020 5:00 pm
(@mynda)
Posts: 4762
Member Admin
 

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

 
Posted : 27/10/2020 7:02 pm
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 

Thanks, I will follow up with end state example to make sure I am clearer.

In meantime, I will do suggested steps.

Regards,

Robb

 
Posted : 28/10/2020 3:32 pm
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 

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

 
Posted : 28/10/2020 4:31 pm
(@mynda)
Posts: 4762
Member Admin
 

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

 
Posted : 28/10/2020 8:15 pm
(@raffrobb)
Posts: 38
Trusted Member
Topic starter
 

Thanks, good to know its doable with these queries.

 

Thanks

Robb

 
Posted : 29/10/2020 6:01 pm
Share: