Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Thread: Power Query column alignment
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Sep 2019
    Location
    United Kingdom
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query column alignment

    Quote Originally Posted by peter789 View Post
    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

  2. #12
    New Member
    Join Date
    Sep 2019
    Location
    United Kingdom
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query column alignment

    Quote Originally Posted by sandy666 View Post
    is that what you want?

    first ten rows...

    [COLOR=#FFFFFF ]country[/COLOR] [COLOR=#FFFFFF ]locality[/COLOR] [COLOR=#FFFFFF ]postal_code[/COLOR] [COLOR=#FFFFFF ]premises[/COLOR] [COLOR=#FFFFFF ]region[/COLOR] [COLOR=#FFFFFF ]country_of_residence[/COLOR] [COLOR=#FFFFFF ]etag[/COLOR] [COLOR=#FFFFFF ]kind[/COLOR] [COLOR=#FFFFFF ]name[/COLOR] [COLOR=#FFFFFF ]nationality[/COLOR] [COLOR=#FFFFFF ]natures_of_control[/COLOR] [COLOR=#FFFFFF ]notified_on[/COLOR] [COLOR=#FFFFFF ]date_of_birth_month[/COLOR] [COLOR=#FFFFFF ]year[/COLOR] [COLOR=#FFFFFF ]links_self[/COLOR] [COLOR=#FFFFFF ]name_elements_forename[/COLOR] [COLOR=#FFFFFF ]middle_name[/COLOR] [COLOR=#FFFFFF ]surname[/COLOR] [COLOR=#FFFFFF ]title[/COLOR]
    England Cheltenham GL52 6JN 25 Gloucestershire England 6b3a4e650b01c2e55f73e24824b955ab0a8f887d individual-person-with-significant-control Mr Nicholas Mark Kennaugh British ownership-of-shares-25-to-50-percent 2016-04-06 8 1976 /company/08593521/persons-with-significant-control/individual/hslAyZBX6yGlqfpnT9fb4qYHCBI Nicholas Mark Kennaugh Mr
    England Cheltenham GL52 8AW 12 Gloucestershire England 0e46ed339a412daa1051f43cf6adb45b1bf690f2 individual-person-with-significant-control Mr Mark Aaron Lynch British ownership-of-shares-75-to-100-percent 2016-04-06 2 1973 /company/05870775/persons-with-significant-control/individual/7rFOZike0t14IwhUmHV0lGGHfPQ Mark Aaron Lynch Mr
    England Cheltenham GL54 2AR Lansdown House Gloucestershire England 11b534c4e0097b7e91a2cc5ae347735736907027 individual-person-with-significant-control Mr Marc Stuart Hardwick British ownership-of-shares-25-to-50-percent 2016-04-06 11 1974 /company/02519387/persons-with-significant-control/individual/WomnabB75hbFk86D1aZEVwFMXqQ Marc Stuart Hardwick Mr
    England Cheltenham GL53 8JU 43 Gloucestershire England a816446d0984d409dccf14acbdf03b1392887210 individual-person-with-significant-control Mr Philip Chakkala Mannil Thomas British ownership-of-shares-50-to-75-percent 2016-04-06 5 1971 /company/07319694/persons-with-significant-control/individual/wEZV0ZC-gQVuxonHcvjSq_eJ1Bg Philip Chakkala Mannil Thomas Mr
    England Cheltenham GL52 9QG Box Farm Gloucestershire England 016c278579773f36fd07a233b0c2fdfcc794e527 individual-person-with-significant-control Mr Roderick Iain Craig British ownership-of-shares-25-to-50-percent 2016-04-06 8 1951 /company/02946363/persons-with-significant-control/individual/1V41OGLbke5wMotCn6Uu4xXY4-w Roderick Iain Craig Mr
    England Cheltenham GL52 3BG Church Farm House Gloucestershire England a1cb778a72e3feaa64a284a37db3bc99c0212188 individual-person-with-significant-control Mr Michael John Whitehead British ownership-of-shares-50-to-75-percent 2016-04-06 4 1955 /company/02952904/persons-with-significant-control/individual/nOMjZBsI7qwdwyAo_XIXVNu3x54 Michael John Whitehead Mr
    England Cheltenham GL53 9BZ 22 Gloucestershire England 8bea1db3b5533c8e9d813df347179a4faee16d25 individual-person-with-significant-control Mr Peter Charles Bygrave British ownership-of-shares-25-to-50-percent 2016-04-06 3 1966 /company/05521937/persons-with-significant-control/individual/XxtpFogryzHg0Uf_QtnsN7KF2Wo Peter Charles Bygrave Mr
    England Cheltenham GL52 6PU Rambling Views Gloucestershire England 1f92f986bf2f02d49e1c43207f83cd9d64291906 individual-person-with-significant-control Mr Kevin Andrew Mullard British ownership-of-shares-25-to-50-percent 2016-04-06 12 1972 /company/07708895/persons-with-significant-control/individual/vpi57ZKbDIS-6sbvrw0WNqleaQg Kevin Andrew Mullard Mr
    England Cheltenham GL52 6TN 15 Gloucestershire England eab3e6baa4c8a0d65a6b1dd6920b6ef1daabc7ac individual-person-with-significant-control Mr James Robert Lewis British ownership-of-shares-75-to-100-percent 2016-04-06 11 1959 /company/09673313/persons-with-significant-control/individual/jiB4zE4EyIQgMLGiMSh2ZbbDY8c James Robert Lewis Mr
    England Cheltenham GL50 3PQ Royal Mews Gloucestershire England 1a8750de30b9a3d2157119f6b17173a55673de14 individual-person-with-significant-control Mr Nigel John Deverson British ownership-of-shares-25-to-50-percent 2016-04-06 4 1963 /company/08311201/persons-with-significant-control/individual/TO04xtFnQbQkqLvZtNIUKnXdFRk Nigel John Deverson Mr


    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

  3. #13
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query column alignment

    this is 868 MB not 5 GB

    use header names what you want split/concatenate

    and don't quote whole post, please!
    Last edited by sandy666; Sep 15th, 2019 at 11:17 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #14
    New Member
    Join Date
    Sep 2019
    Location
    United Kingdom
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query column alignment

    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

  5. #15
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query column alignment

    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 by sandy666; Sep 15th, 2019 at 11:43 AM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #16
    New Member
    Join Date
    Sep 2019
    Location
    United Kingdom
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query column alignment

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

  7. #17
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query column alignment

    it takes some time with ca. 5 GB of data (sorry, I didn't check unzipped file size )
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #18
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,805
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Power Query column alignment

    you can try this

    Code:
    let
        Source = Table.FromColumns({Lines.FromBinary(File.Contents("D:\test\persons-with-significant-control-snapshot-2019-09-15\persons-with-significant-control-snapshot-2019-09-15.txt"), 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
    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
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #19
    Board Regular
    Join Date
    Nov 2016
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query column alignment

    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

  10. #20
    New Member
    Join Date
    Sep 2019
    Location
    United Kingdom
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query column alignment

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

    Ben

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •