Results 1 to 5 of 5

Thread: Concatenate column gets corrupted when query is created

  1. #1
    Acolyte Adnandos's Avatar
    Join Date
    Jun 2018
    Posts
    25
    Articles
    0
    Excel Version
    365

    Concatenate column gets corrupted when query is created



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all

    I have an excel table of about 20 columns, one of which is a concatenate of two other columns with very long numbers:
    • ie Column 1: 10 digit number where the first digit is always zero, concatenated with
    • Column 2: 20 digit other number.


    The concatenate displays just fine in my excel table.

    When I import it through power query, 80% of my rows with concatenate data look as they should, eg 123456789012345678901234567890

    For some reason, about 20% of the rows just appear like this in the query 123456789012345E+28

    Why is this E+28 thing happening?

    All my data in column 1 is exactly 10 digits in length with the leading digit being 0 in every instance, likewise all data in column 2 is exactly 20 digits.

    In power query I've formatted them as text intentionally.

    How can I resolve this?

  2. #2
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016
    The 2 or 3 columns that you concatenated in Excel can be concatenated in PQ by selecting the first, then the second etc and doing the same for the second query. Thus if I needed to concatenate say account and dept and merge this table with a second, I would in PQ select Account then dept on query 1 and then do the same on query 2 (same selection order) and then merge on this essentially dynamic concatenated column. This will probably solve you problem

  3. #3
    Acolyte Adnandos's Avatar
    Join Date
    Jun 2018
    Posts
    25
    Articles
    0
    Excel Version
    365
    Ed your recommendation was great, accurate and saves me many many steps!

    The concats were only there so I could identify and remove "duplicates" given certain columns.

    I've removed them from the excel tables, and used the add custom column feature in PQ - works much better and means I don't have to add a concat manually into every separate table.



    Sent from my SM-T813 using Tapatalk

  4. #4
    Conjurer Ed Kelly's Avatar
    Join Date
    Jul 2016
    Posts
    164
    Articles
    0
    Excel Version
    2016
    And what's not to love about concatenating manually hundreds of workbooks...character building I believe ....and ....isn't it why you got into finance after all!

  5. #5
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,849
    Articles
    0
    Excel Version
    365
    I know this is an old thread; just going through them to learn.
    Quote Originally Posted by Adnandos View Post
    The concats were only there so I could identify and remove "duplicates" given certain columns.
    I've removed them from the excel tables, and used the add custom column feature in PQ - works much better
    Quote Originally Posted by Ed Kelly View Post
    And what's not to love about concatenating manually hundreds of workbooks...character building I believe ....and ....isn't it why you got into finance after all!
    Maybe you don't have to concatenate at all? - you can remove duplicates while referring to multiple columns:
    Code:
    = Table.Distinct(#"Previous Step", {"Data 1", "Data 2", "Store"})

Tags for this Thread

Posting Permissions

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