Append Query

[Dokat]

Hi,

When i try to append 2 tables in Excel it deletes some of the values and give below error message. Can anyone help?

DataFormat.Error: We couldn't convert to Number.
Details:
10002324A


Thank you

 

[Matt Allington]

Well it looks like what ever you are trying to convert is not a number. Make sure there are no spaces, no currency indicators, no commas etc

 

[Dokat]

There are no spaces commas ie however some numbers end with a letter and i cant change that. Is there a way around it?

Thanks

 

[Matt Allington]

Well there is no way to turn 1234A into a number, because it is not a number. If you had 1234A, what number would you expect? Are you expecting 1234?

You could do a "Replace Values" and replace the character A with 'nothing' and that would work. Trouble is you would have to do this step once for every character that could possible appear in the column. There may be another way, but I don't know of it.

 

[MarcelBeug]

In Power Query you can remove the "Changed Type" steps before the append, so the query looks like:

let
    Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Appended Query" = Table.Combine({Source1, Source2})
in
    #"Appended Query"

 

[Dokat]

Thank you