Page 1 of 3 123 LastLast
Results 1 to 10 of 21

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

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

    Default Power Query column alignment

    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=1JM...Ex9kXAP8QofK3T

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

    Default Re: Power Query column alignment

    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'.
    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
    I don't use vba in any form!

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

    Default Re: Power Query column alignment

    Quote Originally Posted by sandy666 View Post
    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=1W7...m9k6dZuDv4kjaA

    Thanks,

    Ben

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

    Default Re: Power Query column alignment

    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.
    Last edited by peter789; Sep 14th, 2019 at 06:56 AM.

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

    Default Re: Power Query column alignment

    I'll wait to see if the post#4 will work
    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
    I don't use vba in any form!

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

    Default Re: Power Query column alignment

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

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

    Default Re: Power Query column alignment

    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

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

    Default Re: Power Query column alignment

    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

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

    Default Re: Power Query column alignment

    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

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

    Cool Re: Power Query column alignment

    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
    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
    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
    I don't use vba in any form!

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
  •