# Power Query column alignment



## jenksdev (Sep 14, 2019)

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 (Sep 14, 2019)

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 (Sep 14, 2019)

sandy666 said:


> 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 (Sep 14, 2019)

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 (Sep 14, 2019)

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


----------



## jenksdev (Sep 15, 2019)

peter789 said:


> This is messy but should work.
> 1  Add index column
> 2  Unpivot other columns, don't aggregate.
> 3  Remove column with previous headers.
> ...



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 (Sep 15, 2019)

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 (Sep 15, 2019)

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 (Sep 15, 2019)

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 (Sep 15, 2019)

is that what you want?

first ten rows...


*country**locality**postal_code**premises**region**country_of_residence**etag**kind**name**nationality**natures_of_control**notified_on**date_of_birth_month**year**links_self**name_elements_forename**middle_name**surname**title*EnglandCheltenhamGL52 6JN25GloucestershireEngland6b3a4e650b01c2e55f73e24824b955ab0a8f887dindividual-person-with-significant-controlMr Nicholas Mark KennaughBritishownership-of-shares-25-to-50-percent2016-04-0681976/company/08593521/persons-with-significant-control/individual/hslAyZBX6yGlqfpnT9fb4qYHCBINicholasMarkKennaughMrEnglandCheltenhamGL52 8AW12GloucestershireEngland0e46ed339a412daa1051f43cf6adb45b1bf690f2individual-person-with-significant-controlMr Mark Aaron LynchBritishownership-of-shares-75-to-100-percent2016-04-0621973/company/05870775/persons-with-significant-control/individual/7rFOZike0t14IwhUmHV0lGGHfPQMarkAaronLynchMrEnglandCheltenhamGL54 2ARLansdown HouseGloucestershireEngland11b534c4e0097b7e91a2cc5ae347735736907027individual-person-with-significant-controlMr Marc Stuart HardwickBritishownership-of-shares-25-to-50-percent2016-04-06111974/company/02519387/persons-with-significant-control/individual/WomnabB75hbFk86D1aZEVwFMXqQMarcStuartHardwickMrEnglandCheltenhamGL53 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 MannilThomasMrEnglandCheltenhamGL52 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-wRoderickIainCraigMrEnglandCheltenhamGL52 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_XIXVNu3x54MichaelJohnWhiteheadMrEnglandCheltenhamGL53 9BZ22GloucestershireEngland8bea1db3b5533c8e9d813df347179a4faee16d25individual-person-with-significant-controlMr Peter Charles BygraveBritishownership-of-shares-25-to-50-percent2016-04-0631966/company/05521937/persons-with-significant-control/individual/XxtpFogryzHg0Uf_QtnsN7KF2WoPeterCharlesBygraveMrEnglandCheltenhamGL52 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-6sbvrw0WNqleaQgKevinAndrewMullardMrEnglandCheltenhamGL52 6TN15GloucestershireEnglandeab3e6baa4c8a0d65a6b1dd6920b6ef1daabc7acindividual-person-with-significant-controlMr James Robert LewisBritishownership-of-shares-75-to-100-percent2016-04-06111959/company/09673313/persons-with-significant-control/individual/jiB4zE4EyIQgMLGiMSh2ZbbDY8cJamesRobertLewisMrEnglandCheltenhamGL50 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


----------



## jenksdev (Sep 14, 2019)

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


----------



## jenksdev (Sep 15, 2019)

peter789 said:


> 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



Hi Peter,

It's from here: http://download.companieshouse.gov.uk/en_pscdata.html - PSC data as one file

It's a 5GB file though, and it downloads as a .txt

Thank you for your help on this one!

Ben


----------



## jenksdev (Sep 15, 2019)

sandy666 said:


> is that what you want?
> 
> first ten rows...
> 
> ...




This is almost exactly what I am looking for, yes! 

I would need the company number, and business address though, concatenated from the address_line_1, address_line_2, locality etc.

Thanks,

Ben


----------



## sandy666 (Sep 15, 2019)

this is 868 MB not 5 GB 

use header names what you want split/concatenate

and don't quote whole post, please!


----------



## jenksdev (Sep 15, 2019)

Sorry, the only option it was giving me was to reply with whole quote.

Would you mind explaining how you got to that end result?

Thanks,

Ben


----------



## sandy666 (Sep 15, 2019)

use Reply not Reply With Quote 







your previous source is incorrect because there is no company number, address lin1, etc... , I am trying with original file from the link

but you can do that yourself with some splits, replaces and Unpivot and Pivot


----------



## jenksdev (Sep 15, 2019)

Sorry, I hadn't realised that the smaller extract cut out company number.


----------



## sandy666 (Sep 15, 2019)

it takes some time with ca. 5 GB of data (sorry, I didn't check unzipped file size )


----------



## sandy666 (Sep 15, 2019)

you can try this


```
[SIZE=1]let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("[B][COLOR="#FF0000"]D:\test\persons-with-significant-control-snapshot-2019-09-15\persons-with-significant-control-snapshot-2019-09-15.txt[/COLOR][/B]"), null, null, 1252)}),
    RV1 = Table.ReplaceValue(Source,"percent"",""voting","percent_voting",Replacer.ReplaceText,{"Column1"}),
    Split1 = Table.SplitColumn(RV1, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29"}),
    RV2 = Table.ReplaceValue(Split1,":{","_",Replacer.ReplaceText,{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29"}),
    RV3 = Table.ReplaceValue(RV2,"}","",Replacer.ReplaceText,{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29"}),
    RV4 = Table.ReplaceValue(RV3,"{","",Replacer.ReplaceText,{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12", "Column1.13", "Column1.14", "Column1.15", "Column1.16", "Column1.17", "Column1.18", "Column1.19", "Column1.20", "Column1.21", "Column1.22", "Column1.23", "Column1.24", "Column1.25", "Column1.26", "Column1.27", "Column1.28", "Column1.29"}),
    Index = Table.AddIndexColumn(RV4, "Index", 1, 1),
    UnpivotOC = Table.UnpivotOtherColumns(Index, {"Index"}, "Attribute", "Value"),
    RC = Table.RemoveColumns(UnpivotOC,{"Attribute"}),
    Split2 = Table.SplitColumn(RC, "Value", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
    Pivot = Table.Pivot(Split2, List.Distinct(Split2[Value.1]), "Value.1", "Value.2")
in
    Pivot[/SIZE]
```

change path (red line) to your source file

I suggest to use parts of this file from url you posted because whole source file is too big to see result in short time


----------



## peter789 (Sep 15, 2019)

Ben,
After a lot of messing about because I knew nothing about JSON files I came up with this:

```
let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\Peter\Documents\PowerQuery\persons-with-significant-control-snapshot-2019-09-15.txt"), null, null, 1252)}),
    #"Parsed JSON" = Table.TransformColumns(Source,{},Json.Document),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1", {"company_number", "data"}, {"company_number", "data"}),
    #"Expanded data" = Table.ExpandRecordColumn(#"Expanded Column1", "data", {"address", "ceased_on", "country_of_residence", "date_of_birth", "etag", "kind", "links", "name", "name_elements", "nationality", "natures_of_control", "notified_on"}, {"address", "ceased_on", "country_of_residence", "date_of_birth", "etag", "kind", "links", "name", "name_elements", "nationality", "natures_of_control", "notified_on"}),
    #"Expanded date_of_birth" = Table.ExpandRecordColumn(#"Expanded data", "date_of_birth", {"month", "year"}, {"month", "year"}),
    #"Expanded links" = Table.ExpandRecordColumn(#"Expanded date_of_birth", "links", {"self"}, {"self"}),
    #"Expanded name_elements" = Table.ExpandRecordColumn(#"Expanded links", "name_elements", {"forename", "middle_name", "surname", "title"}, {"forename", "middle_name", "surname", "title"}),
    #"Extracted Values" = Table.TransformColumns(#"Expanded name_elements", {"natures_of_control", each Text.Combine(List.Transform(_, Text.From)), type text}),
    #"Expanded address" = Table.ExpandRecordColumn(#"Extracted Values", "address", {"address_line_1", "country", "locality", "postal_code", "premises", "address_line_2", "region"}, {"address_line_1", "country", "locality", "postal_code", "premises", "address_line_2", "region"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded address",{{"company_number", type text}, {"address_line_1", type text}, {"country", type text}, {"locality", type text}, {"postal_code", type text}, {"premises", type text}, {"address_line_2", type text}, {"region", type text}, {"ceased_on", type date}, {"country_of_residence", type text}, {"month", Int64.Type}, {"year", Int64.Type}, {"etag", type text}, {"kind", type text}, {"self", type text}, {"name", type text}, {"forename", type text}, {"middle_name", type text}, {"surname", type text}, {"title", type text}, {"nationality", type text}, {"natures_of_control", type text}, {"notified_on", type date}})
in
    #"Changed Type"
```

hope it works for you.
Peter


----------



## jenksdev (Sep 17, 2019)

Both solutions worked extremely well; thank you so much for your help. It's greatly appreciated!

Ben


----------



## jenksdev (Sep 14, 2019)

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 (Sep 17, 2019)

you are welcome

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

have a nice day


----------

