Power Query column alignment

J

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
 
Sort by date Sort by votes
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
Click to expand...

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
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
sandy666 said:
is that what you want?

first ten rows...

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]country[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]locality[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]postal_code[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]premises[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]region[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]country_of_residence[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]etag[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]kind[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]name[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]nationality[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]natures_of_control[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]notified_on[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]date_of_birth_month[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]year[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]links_self[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]name_elements_forename[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]middle_name[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]surname[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]title[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[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]
</tbody>[/TABLE]


all blank columns are removed
Click to expand...


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
 
Upvote 0
this is 868 MB not 5 GB :)

use header names what you want split/concatenate

and don't quote whole post, please!
 
Last edited:
Upvote 0
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
 
Upvote 0
use Reply not Reply With Quote :)

screenshot-106.png


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
 
Last edited:
Upvote 0
it takes some time with ca. 5 GB of data (sorry, I didn't check unzipped file size :))
 
Upvote 0
you can try this

Code: 
[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
 
Upvote 0
Ben,
After a lot of messing about because I knew nothing about JSON files I came up with this:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Get Data From Power Source | Query Name in Header
Replies
4
Views
220
rachel06
R
K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
517
Kariba
K
J
  • Solved
Power Query: Promoting/Demoting Headers
Replies
2
Views
523
jajatidev
J
Guzzlr
  • Question Question
Power Query Question
Replies
1
Views
196
RoryA
RoryA
R
  • Question Question
Quicken QIF files to Power Query
2
Replies
14
Views
1K
smozgur
smozgur

Forum statistics

Threads
1,224,813
Messages
6,181,109
Members
453,021
Latest member
Justyna P

Share this page

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