Forum Discussion

Andreaal's avatar
Andreaal
New Member
4 years ago
Solved

Combined text and number data in one single column

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

IDSales Qty
2100.10100
ADF-G7615200
3100.10300
4100.20400

 

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

IDDemand Qty
2100.1110
ADF-G7615200
3100.1300
4100.2450

 

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

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    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/

1 Reply

  • tackytechtom's avatar
    tackytechtom
    Most Valuable Professional
    4 years ago

    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/