Concatenate column gets corrupted when query is created

Adnandos

New member
Joined
Jun 12, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
365
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?
 
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
 
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
 
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!
 
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:
Code:
= Table.Distinct(#"Previous Step", {"Data 1", "Data 2", "Store"})
 
Back
Top