Combined text and number data in one single column

[Andreaal]

Does anyone know how to change the data type of a column from text to "Text and number"?

I have a SAP Report that I have already uploaded to my Power Query and a column that contains IDs in both number and text+number formats, in this table the ID column is directly imported as text in Power BI. Example:

Table1

ID	Sales Qty
2100.10	100
ADF-G7615	200
3100.10	300
4100.20	400

And I have another Excel data table with my "master data" that contains the same ID column, but when I import this excel table, Power BI automatically displays the column type as: text and number (123ABC) 

Table2

ID	Demand Qty
2100.1	110
ADF-G7615	200
3100.1	300
4100.2	450

The problem is that Table1 is as text and the ID is represented as example: 2100.10 and in Table2 as decimal number 2100.1 - so it  eliminates the zero at the end, therefore when I try to relate the columns, it displays error. 

* I tried changing the column in my Excel File to text format to make sure I have the zeros, but when I upload it to the Power Query it still changes the data type to 123ABC and the zeros disappear.

* I tried changing the data type to text but it still does not display the zeros. 

* I also tried changing Table1 to number type but displays error for the IDs that combine text+number.

Can anybody help me figure out a way to make sure both columns contain the same values? 

Thank you very much

[tackytechtom · Accepted solution]

Hi @Andreaal ,

If I understand your query correctly, you would like to add back the 0. How about this:

Before:

After:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI0MNAzVNJRMjQ0UIrViVZydHHTdTc3MzQFihkZQMSMYYqMoQImIAEjoICJKVAgFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Demand Qty" = _t]),
    #"Replace Values" = Table.ReplaceValue(Source, each [ID], each try Number.ToText ( Number.From([ID]), "f" ) otherwise [ID], Replacer.ReplaceValue,{"ID"})

in
    #"Replace Values"

Let me know, if this solves your issue 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/