Power Query column alignment

jenksdev

New Member
Joined
Sep 14, 2019
Messages
10
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jenksdev

New Member
Joined
Sep 14, 2019
Messages
10
is that what you want?

first ten rows...

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

<tbody>
</tbody>


all blank columns are removed

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

Well-known Member
Joined
Oct 24, 2015
Messages
4,660
this is 868 MB not 5 GB :)

use header names what you want split/concatenate

and don't quote whole post, please!
 
Last edited:

jenksdev

New Member
Joined
Sep 14, 2019
Messages
10
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

Well-known Member
Joined
Oct 24, 2015
Messages
4,660
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
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,660
it takes some time with ca. 5 GB of data (sorry, I didn't check unzipped file size :))
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,660
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
 

peter789

Board Regular
Joined
Nov 20, 2016
Messages
114
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
 

jenksdev

New Member
Joined
Sep 14, 2019
Messages
10
Both solutions worked extremely well; thank you so much for your help. It's greatly appreciated!

Ben
 

Forum statistics

Threads
1,089,200
Messages
5,406,794
Members
403,106
Latest member
AliO

This Week's Hot Topics

Top