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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
you forgot about one thing: DataSource.Error: Could not find a part of the path 'C:\Users\Ben\Desktop\PQ\persons-with-significant-control-snapshot-2019-09-13.csv'.
 
Upvote 0
This is messy but should work.
1 Add index column
2 Unpivot other columns, don't aggregate.
3 Remove column with previous headers.
4 Split column of data at first colon.
5 Re-pivot

Peter

PS I missed your last post. Solution based on first sheet of data.
 
Last edited:
Upvote 0
This is messy but should work.
1 Add index column
2 Unpivot other columns, don't aggregate.
3 Remove column with previous headers.
4 Split column of data at first colon.
5 Re-pivot

Peter

PS I missed your last post. Solution based on first sheet of data.

Thanks Peter.

Having a small issue, probably me being an idiot, but this is the result after step five...

https://imgur.com/a/JdSpdRP

Is it obvious what I have done wrong?

Thanks,

Ben
 
Upvote 0
Ben,
I should have added that when you Pivot in the last step you need to click advanced and select "Don't aggregate" at the bottom of the drop down list.

Peter
 
Upvote 0
Ben,
I've just rechecked the results and have found a few more problems with the data. It appears the problem is with the source. Although it looks like a CSV file the text in the fields also contain commas so when opened in Excel the fields are being split up. Would it be possible to give a link to the source of the raw data please?

Peter
 
Upvote 0
Ben,
Am I correct in thinking this is a JSON format file?
Could I suggest you save the file to your PC then connect to PQ with the from web option. I haven't used this technique myself but for the URL you need to type file:\followed by the path to your .json file.

Peter
 
Upvote 0
is that what you want?

first ten rows...

countrylocalitypostal_codepremisesregioncountry_of_residenceetagkindnamenationalitynatures_of_controlnotified_ondate_of_birth_monthyearlinks_selfname_elements_forenamemiddle_namesurnametitle
EnglandCheltenhamGL52 6JN25GloucestershireEngland6b3a4e650b01c2e55f73e24824b955ab0a8f887dindividual-person-with-significant-controlMr Nicholas Mark KennaughBritishownership-of-shares-25-to-50-percent2016-04-0681976/company/08593521/persons-with-significant-control/individual/hslAyZBX6yGlqfpnT9fb4qYHCBINicholasMarkKennaughMr
EnglandCheltenhamGL52 8AW12GloucestershireEngland0e46ed339a412daa1051f43cf6adb45b1bf690f2individual-person-with-significant-controlMr Mark Aaron LynchBritishownership-of-shares-75-to-100-percent2016-04-0621973/company/05870775/persons-with-significant-control/individual/7rFOZike0t14IwhUmHV0lGGHfPQMarkAaronLynchMr
EnglandCheltenhamGL54 2ARLansdown HouseGloucestershireEngland11b534c4e0097b7e91a2cc5ae347735736907027individual-person-with-significant-controlMr Marc Stuart HardwickBritishownership-of-shares-25-to-50-percent2016-04-06111974/company/02519387/persons-with-significant-control/individual/WomnabB75hbFk86D1aZEVwFMXqQMarcStuartHardwickMr
EnglandCheltenhamGL53 8JU43GloucestershireEnglanda816446d0984d409dccf14acbdf03b1392887210individual-person-with-significant-controlMr Philip Chakkala Mannil ThomasBritishownership-of-shares-50-to-75-percent2016-04-0651971/company/07319694/persons-with-significant-control/individual/wEZV0ZC-gQVuxonHcvjSq_eJ1BgPhilipChakkala MannilThomasMr
EnglandCheltenhamGL52 9QGBox FarmGloucestershireEngland016c278579773f36fd07a233b0c2fdfcc794e527individual-person-with-significant-controlMr Roderick Iain CraigBritishownership-of-shares-25-to-50-percent2016-04-0681951/company/02946363/persons-with-significant-control/individual/1V41OGLbke5wMotCn6Uu4xXY4-wRoderickIainCraigMr
EnglandCheltenhamGL52 3BGChurch Farm HouseGloucestershireEnglanda1cb778a72e3feaa64a284a37db3bc99c0212188individual-person-with-significant-controlMr Michael John WhiteheadBritishownership-of-shares-50-to-75-percent2016-04-0641955/company/02952904/persons-with-significant-control/individual/nOMjZBsI7qwdwyAo_XIXVNu3x54MichaelJohnWhiteheadMr
EnglandCheltenhamGL53 9BZ22GloucestershireEngland8bea1db3b5533c8e9d813df347179a4faee16d25individual-person-with-significant-controlMr Peter Charles BygraveBritishownership-of-shares-25-to-50-percent2016-04-0631966/company/05521937/persons-with-significant-control/individual/XxtpFogryzHg0Uf_QtnsN7KF2WoPeterCharlesBygraveMr
EnglandCheltenhamGL52 6PURambling ViewsGloucestershireEngland1f92f986bf2f02d49e1c43207f83cd9d64291906individual-person-with-significant-controlMr Kevin Andrew MullardBritishownership-of-shares-25-to-50-percent2016-04-06121972/company/07708895/persons-with-significant-control/individual/vpi57ZKbDIS-6sbvrw0WNqleaQgKevinAndrewMullardMr
EnglandCheltenhamGL52 6TN15GloucestershireEnglandeab3e6baa4c8a0d65a6b1dd6920b6ef1daabc7acindividual-person-with-significant-controlMr James Robert LewisBritishownership-of-shares-75-to-100-percent2016-04-06111959/company/09673313/persons-with-significant-control/individual/jiB4zE4EyIQgMLGiMSh2ZbbDY8cJamesRobertLewisMr
EnglandCheltenhamGL50 3PQRoyal MewsGloucestershireEngland1a8750de30b9a3d2157119f6b17173a55673de14individual-person-with-significant-controlMr Nigel John DeversonBritishownership-of-shares-25-to-50-percent2016-04-0641963/company/08311201/persons-with-significant-control/individual/TO04xtFnQbQkqLvZtNIUKnXdFRkNigelJohnDeversonMr

all blank columns are removed
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

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