Power Query column alignment

[jenksdev]

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

 

[sandy666]

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'.

 

[jenksdev]

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'.

Haha, good point. Schoolboy error.

I've extracted the data and popped into a file that just gets its data from a table.

https://drive.google.com/open?id=1W7zp9uuepEjAduv9bNm9k6dZuDv4kjaA

Thanks,

Ben

 

[peter789]

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.

 

[sandy666]

I'll wait to see if the post#4 will work

 

[jenksdev]

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

 

[peter789]

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

 

[peter789]

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

 

[peter789]

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

 

[sandy666]

is that what you want?

first ten rows...

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]country[/td][td=bgcolor:#70AD47]locality[/td][td=bgcolor:#70AD47]postal_code[/td][td=bgcolor:#70AD47]premises[/td][td=bgcolor:#70AD47]region[/td][td=bgcolor:#70AD47]country_of_residence[/td][td=bgcolor:#70AD47]etag[/td][td=bgcolor:#70AD47]kind[/td][td=bgcolor:#70AD47]name[/td][td=bgcolor:#70AD47]nationality[/td][td=bgcolor:#70AD47]natures_of_control[/td][td=bgcolor:#70AD47]notified_on[/td][td=bgcolor:#70AD47]date_of_birth_month[/td][td=bgcolor:#70AD47]year[/td][td=bgcolor:#70AD47]links_self[/td][td=bgcolor:#70AD47]name_elements_forename[/td][td=bgcolor:#70AD47]middle_name[/td][td=bgcolor:#70AD47]surname[/td][td=bgcolor:#70AD47]title[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]Cheltenham[/td][td=bgcolor:#E2EFDA]GL52 6JN[/td][td=bgcolor:#E2EFDA]25[/td][td=bgcolor:#E2EFDA]Gloucestershire[/td][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]6b3a4e650b01c2e55f73e24824b955ab0a8f887d[/td][td=bgcolor:#E2EFDA]individual-person-with-significant-control[/td][td=bgcolor:#E2EFDA]Mr Nicholas Mark Kennaugh[/td][td=bgcolor:#E2EFDA]British[/td][td=bgcolor:#E2EFDA]ownership-of-shares-25-to-50-percent[/td][td=bgcolor:#E2EFDA]2016-04-06[/td][td=bgcolor:#E2EFDA]8[/td][td=bgcolor:#E2EFDA]1976[/td][td=bgcolor:#E2EFDA]/company/08593521/persons-with-significant-control/individual/hslAyZBX6yGlqfpnT9fb4qYHCBI[/td][td=bgcolor:#E2EFDA]Nicholas[/td][td=bgcolor:#E2EFDA]Mark[/td][td=bgcolor:#E2EFDA]Kennaugh[/td][td=bgcolor:#E2EFDA]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td]England[/td][td]Cheltenham[/td][td]GL52 8AW[/td][td]12[/td][td]Gloucestershire[/td][td]England[/td][td]0e46ed339a412daa1051f43cf6adb45b1bf690f2[/td][td]individual-person-with-significant-control[/td][td]Mr Mark Aaron Lynch[/td][td]British[/td][td]ownership-of-shares-75-to-100-percent[/td][td]2016-04-06[/td][td]2[/td][td]1973[/td][td]/company/05870775/persons-with-significant-control/individual/7rFOZike0t14IwhUmHV0lGGHfPQ[/td][td]Mark[/td][td]Aaron[/td][td]Lynch[/td][td]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]Cheltenham[/td][td=bgcolor:#E2EFDA]GL54 2AR[/td][td=bgcolor:#E2EFDA]Lansdown House[/td][td=bgcolor:#E2EFDA]Gloucestershire[/td][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]11b534c4e0097b7e91a2cc5ae347735736907027[/td][td=bgcolor:#E2EFDA]individual-person-with-significant-control[/td][td=bgcolor:#E2EFDA]Mr Marc Stuart Hardwick[/td][td=bgcolor:#E2EFDA]British[/td][td=bgcolor:#E2EFDA]ownership-of-shares-25-to-50-percent[/td][td=bgcolor:#E2EFDA]2016-04-06[/td][td=bgcolor:#E2EFDA]11[/td][td=bgcolor:#E2EFDA]1974[/td][td=bgcolor:#E2EFDA]/company/02519387/persons-with-significant-control/individual/WomnabB75hbFk86D1aZEVwFMXqQ[/td][td=bgcolor:#E2EFDA]Marc[/td][td=bgcolor:#E2EFDA]Stuart[/td][td=bgcolor:#E2EFDA]Hardwick[/td][td=bgcolor:#E2EFDA]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td]England[/td][td]Cheltenham[/td][td]GL53 8JU[/td][td]43[/td][td]Gloucestershire[/td][td]England[/td][td]a816446d0984d409dccf14acbdf03b1392887210[/td][td]individual-person-with-significant-control[/td][td]Mr Philip Chakkala Mannil Thomas[/td][td]British[/td][td]ownership-of-shares-50-to-75-percent[/td][td]2016-04-06[/td][td]5[/td][td]1971[/td][td]/company/07319694/persons-with-significant-control/individual/wEZV0ZC-gQVuxonHcvjSq_eJ1Bg[/td][td]Philip[/td][td]Chakkala Mannil[/td][td]Thomas[/td][td]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]Cheltenham[/td][td=bgcolor:#E2EFDA]GL52 9QG[/td][td=bgcolor:#E2EFDA]Box Farm[/td][td=bgcolor:#E2EFDA]Gloucestershire[/td][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]016c278579773f36fd07a233b0c2fdfcc794e527[/td][td=bgcolor:#E2EFDA]individual-person-with-significant-control[/td][td=bgcolor:#E2EFDA]Mr Roderick Iain Craig[/td][td=bgcolor:#E2EFDA]British[/td][td=bgcolor:#E2EFDA]ownership-of-shares-25-to-50-percent[/td][td=bgcolor:#E2EFDA]2016-04-06[/td][td=bgcolor:#E2EFDA]8[/td][td=bgcolor:#E2EFDA]1951[/td][td=bgcolor:#E2EFDA]/company/02946363/persons-with-significant-control/individual/1V41OGLbke5wMotCn6Uu4xXY4-w[/td][td=bgcolor:#E2EFDA]Roderick[/td][td=bgcolor:#E2EFDA]Iain[/td][td=bgcolor:#E2EFDA]Craig[/td][td=bgcolor:#E2EFDA]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td]England[/td][td]Cheltenham[/td][td]GL52 3BG[/td][td]Church Farm House[/td][td]Gloucestershire[/td][td]England[/td][td]a1cb778a72e3feaa64a284a37db3bc99c0212188[/td][td]individual-person-with-significant-control[/td][td]Mr Michael John Whitehead[/td][td]British[/td][td]ownership-of-shares-50-to-75-percent[/td][td]2016-04-06[/td][td]4[/td][td]1955[/td][td]/company/02952904/persons-with-significant-control/individual/nOMjZBsI7qwdwyAo_XIXVNu3x54[/td][td]Michael[/td][td]John[/td][td]Whitehead[/td][td]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]Cheltenham[/td][td=bgcolor:#E2EFDA]GL53 9BZ[/td][td=bgcolor:#E2EFDA]22[/td][td=bgcolor:#E2EFDA]Gloucestershire[/td][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]8bea1db3b5533c8e9d813df347179a4faee16d25[/td][td=bgcolor:#E2EFDA]individual-person-with-significant-control[/td][td=bgcolor:#E2EFDA]Mr Peter Charles Bygrave[/td][td=bgcolor:#E2EFDA]British[/td][td=bgcolor:#E2EFDA]ownership-of-shares-25-to-50-percent[/td][td=bgcolor:#E2EFDA]2016-04-06[/td][td=bgcolor:#E2EFDA]3[/td][td=bgcolor:#E2EFDA]1966[/td][td=bgcolor:#E2EFDA]/company/05521937/persons-with-significant-control/individual/XxtpFogryzHg0Uf_QtnsN7KF2Wo[/td][td=bgcolor:#E2EFDA]Peter[/td][td=bgcolor:#E2EFDA]Charles[/td][td=bgcolor:#E2EFDA]Bygrave[/td][td=bgcolor:#E2EFDA]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td]England[/td][td]Cheltenham[/td][td]GL52 6PU[/td][td]Rambling Views[/td][td]Gloucestershire[/td][td]England[/td][td]1f92f986bf2f02d49e1c43207f83cd9d64291906[/td][td]individual-person-with-significant-control[/td][td]Mr Kevin Andrew Mullard[/td][td]British[/td][td]ownership-of-shares-25-to-50-percent[/td][td]2016-04-06[/td][td]12[/td][td]1972[/td][td]/company/07708895/persons-with-significant-control/individual/vpi57ZKbDIS-6sbvrw0WNqleaQg[/td][td]Kevin[/td][td]Andrew[/td][td]Mullard[/td][td]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]Cheltenham[/td][td=bgcolor:#E2EFDA]GL52 6TN[/td][td=bgcolor:#E2EFDA]15[/td][td=bgcolor:#E2EFDA]Gloucestershire[/td][td=bgcolor:#E2EFDA]England[/td][td=bgcolor:#E2EFDA]eab3e6baa4c8a0d65a6b1dd6920b6ef1daabc7ac[/td][td=bgcolor:#E2EFDA]individual-person-with-significant-control[/td][td=bgcolor:#E2EFDA]Mr James Robert Lewis[/td][td=bgcolor:#E2EFDA]British[/td][td=bgcolor:#E2EFDA]ownership-of-shares-75-to-100-percent[/td][td=bgcolor:#E2EFDA]2016-04-06[/td][td=bgcolor:#E2EFDA]11[/td][td=bgcolor:#E2EFDA]1959[/td][td=bgcolor:#E2EFDA]/company/09673313/persons-with-significant-control/individual/jiB4zE4EyIQgMLGiMSh2ZbbDY8c[/td][td=bgcolor:#E2EFDA]James[/td][td=bgcolor:#E2EFDA]Robert[/td][td=bgcolor:#E2EFDA]Lewis[/td][td=bgcolor:#E2EFDA]Mr[/td][/tr]

[tr=bgcolor:#FFFFFF][td]England[/td][td]Cheltenham[/td][td]GL50 3PQ[/td][td]Royal Mews[/td][td]Gloucestershire[/td][td]England[/td][td]1a8750de30b9a3d2157119f6b17173a55673de14[/td][td]individual-person-with-significant-control[/td][td]Mr Nigel John Deverson[/td][td]British[/td][td]ownership-of-shares-25-to-50-percent[/td][td]2016-04-06[/td][td]4[/td][td]1963[/td][td]/company/08311201/persons-with-significant-control/individual/TO04xtFnQbQkqLvZtNIUKnXdFRk[/td][td]Nigel[/td][td]John[/td][td]Deverson[/td][td]Mr[/td][/tr]
[/table]

all blank columns are removed