Forum Discussion

SandoK's avatar
SandoK
Regular Visitor
1 year ago
Solved

[Data.Format]Error we couldn't convert to number

I have created the custom function "Asset Booking-WorkSheetFunction" of the steps which are going to be repeated on a daily basis.  I am invoking this function in another query called CombinedData. T...
Need Help
  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi SandoK ,

    Thanks for reaching out to the Microsoft fabric community forum. 

     

    Before converting to number, ensure only numeric values are retained. You can modify the "Parent" column step like this:

    Replace this:
    #"Extarcted Parent from customer name" = Table.AddColumn(#"Removed rows for total and Internal cutomer", "Parent", each Text.End([Customer Name],6)),

    With this:
    #"Extarcted Parent from customer name" = Table.AddColumn(#"Removed rows for total and Internal cutomer", "Parent", each try Number.FromText(Text.End([Customer Name],6)) otherwise null),

    And you can now skip changing its type, since non-numeric values will be null.

    Note: Filter out nulls if needed

    If null values in "Parent" aren't desired downstream:
    #"Filtered valid parent values" = Table.SelectRows(#"Extarcted Parent from customer name", each [Parent] <> null)

     

    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards

     

  • v-dineshya's avatar
    v-dineshya
    1 year ago

    Hi SandoK ,

    If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
    Thanks and Regards

MarkLaf's avatar
MarkLaf
Super User
1 year ago

Based on your error message, it seems most likely that the following steps are causing the error:

 

#"Extarcted Parent from customer name" = Table.AddColumn(
    #"Removed rows for total and Internal cutomer", "Parent", each Text.End([Customer Name], 6)
),
#"Changed Parent Col Type to Whole number" = Table.TransformColumnTypes(
    #"Extarcted Parent from customer name", {{"Parent", Int64.Type}}
)

 

It looks like you are assuming there are numbers appended to the end of [Customer Name], which you try to extract and then convert to integer. Based on how you are interacting with [Customer Name] in previous steps, it does not seem like numbers are appended in any cases. Even if there are some rows where you have numbers appended, we can tell from the error that you have at least one instance where, at row level:

  • [Customer Name] = "Oman"
  • Text.End("Oman", 6) = "Oman"
  • Int64.From( "Oman" ) =
    DataFormat.Error: We couldn't convert to Number.
    Details:
    Oman
  • SandoK's avatar
    SandoK
    Regular Visitor
    1 year ago

    Thank you MarkLaf for the response.