Concatenate column gets corrupted when query is created

[Adnandos]

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?

 

[Ed Kelly]

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

 

[Adnandos]

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

 

[Ed Kelly]

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!

 

[p45cal]

I know this is an old thread; just going through them to learn.

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 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:

= Table.Distinct(#"Previous Step", {"Data 1", "Data 2", "Store"})