Power Query column alignment

jenksdev

New Member
Joined
Sep 14, 2019
Messages
10
Hello.

First time here, I hope I'm posting this in the right area.

Please note that while the linked document contains names and addresses, it from a public database and is therefore not a breach of any kind of data protection (United Kingdom's company database from the official government website).

Anyway, what I am struggling with is the inconsistent data output. In the attached file, taking row 1353 as an example, the data shows 'country_of_residence' in the column headed 'premises'.

What I am trying to figure out is how to create a set of steps / queries in order to correctly align the data based on the column headers, as the headers will not change. Once the data is properly aligned, I will then cleanse it and remove all speech marks etc - the easy bit.

I've toyed with it, and can filter out the records that do not match the column headers, but I cannot figure out how to reassign them to the correct column.

Please could someone offer an insight in to how this is possible?

Thanks,

Ben

File is downloadable from Google Drive (in .xlsx format) here: https://drive.google.com/open?id=1JMtxILfIvSCDfoTboqEx9kXAP8QofK3T
 
you are welcome

use solution with Parsed JSON, much easier and faster (with whole 5 GB file)

have a nice day
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top