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

[SandoK]

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. The TransformedData column in the 3rd screenshot shows error for the table

In the custom function in the step  #"Booking Location" which is a conditional column a new condition else if [Customer Name] = "Oman" then "Oman" was added recently. After adding this I started receiveing the error "[Data.Format]Error we couldn't convert to number". I am unable to resolve the same. Need urgent help

Below is the custom function

(sheetname)=>
let
#"Removed Top Rows" = Table.Skip(sheetname,6),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{" ", "Customer Name"}}),
#"Removed Other columns than in position 0 and 4" = Table.SelectColumns(#"Renamed Columns",{Table.ColumnNames(#"Renamed Columns"){0},Table.ColumnNames(#"Renamed Columns"){4}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Other columns than in position 0 and 4", {"Customer Name"}, "Attribute", "Value"),
#"Cleaned Text" = Table.TransformColumns(#"Unpivoted Columns",{{"Attribute", Text.Clean, type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Cleaned Text",{{"Attribute", "Text_Date"}}),
#"Extracted Text Range" = Table.TransformColumns(#"Renamed Columns1", {{"Text_Date", each Text.Middle(_, 6, 15), type text}}),
#"Extracted Date" = Table.AddColumn(#"Extracted Text Range", "Date", each Date.FromText([Text_Date])),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Date",{{"Date", type date}}),
#"Removed Text Date Column" = Table.SelectColumns(#"Changed Type",{Table.ColumnNames(#"Changed Type"){0},Table.ColumnNames(#"Changed Type"){2},Table.ColumnNames(#"Changed Type"){3}}),
#"Booking Location" = Table.AddColumn(#"Removed Text Date Column", "Location", each if [Customer Name] = "Saudi Arabia" then "Saudi Arabia" else if [Customer Name] = "Bahrain" then "Bahrain" else if [Customer Name] = "London" then "London" else if [Customer Name] = "Abu Dhabi" then "Abu Dhabi" else if [Customer Name] = "Oman" then "Oman" else if [Customer Name] = "New York" then "New York" else if [Customer Name] = null then "Performing - Core" else if [Customer Name] = "Staff Loans" then "Retail Banking" else if [Customer Name] = "Headquarters" then "Retail Banking" else if [Customer Name] = "Retail Banking" then "Retail Banking" else if [Customer Name] = "Legacy" then "Legacy" else if [Customer Name] = "Performing Loans" then "Performing Loans" else if [Customer Name] = "Non-performing loans" then "Non-performing loans" else if [Customer Name] = "Gross Loans" then "Gross Loans" else null),
#"Filled Up" = Table.FillUp(#"Booking Location",{"Location"}),
#"Loans-Perf_Ret_Legacy_NonPerf" = Table.AddColumn(#"Filled Up", "Loans", each if [Location] = "Performing - Core" then "Performing - Core" else if [Location] = "Staff Loans" then "Headquarters" else if [Location] = "Headquarters" then "Headquarters" else if [Location] = "Retail Banking" then "Retail Banking" else if [Location] = "Legacy" then "Performing - Legacy" else if [Location] = "Performing loans" then "Performing loans" else if [Location] = "Non-performing loans" then "Non-performing loans" else if [Location] = "Gross Loans" then "Gross Loans" else null),
#"Filled Up1" = Table.FillUp(#"Loans-Perf_Ret_Legacy_NonPerf",{"Loans"}),
#"Removed rows for total and Internal cutomer" = Table.SelectRows(#"Filled Up1", each [Customer Name] <> null and [Customer Name] <> " " and [Customer Name] <> "Saudi Arabia" and [Customer Name] <> "Bahrain" and [Customer Name] <> "London" and [Customer Name] <> "Abu Dhabi" and [Customer Name] <> "New York" and [Customer Name] <> "Staff Loans" and [Customer Name] <> "Headquarters" and [Customer Name] <> "Retail Banking" and [Customer Name] <> "Legacy" and [Customer Name] <> "Performing loans" and [Customer Name] <> "Non-performing loans" and [Customer Name] <> "Gross Loans" and [Customer Name] <> "Internal customer - 0"),
#"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}}),
#"Kept parent no above ******" = Table.SelectRows(#"Changed Parent Col Type to Whole number", each [Parent] > ******),
#"Merged Queries" = Table.NestedJoin(#"Kept parent no above ******", {"Parent"}, Parent_RM_Team, {"Parent"}, "Parent_RM_Team", JoinKind.LeftOuter),
#"Expanded Parent_RM_Team" = Table.ExpandTableColumn(#"Merged Queries", "Parent_RM_Team", {"RM", "Team"}, {"RM", "Coverage_Unit"}),
#"Booking Entity Col based on location" = Table.AddColumn(#"Expanded Parent_RM_Team", "Booking_Entity", each if [Location] = "Saudi Arabia" then "KSA" else if [Location] = "Retail Banking" then "KSA" else "BSC"),
#"Excluded Non perf loans" = Table.SelectRows(#"Booking Entity Col based on location", each ([Loans] <> "Non-performing loans")),
#"Conditional Col - Booking and coverage unit" = Table.AddColumn(#"Excluded Non perf loans", "Booking_CoverageUnit", each if [Booking_Entity] = "KSA" and Text.StartsWith([Coverage_Unit],"KSA") then [Coverage_Unit]
else
if [Booking_Entity] = "KSA" and Text.StartsWith([Coverage_Unit],"FIG") then "KSA-FIG"
else
if [Booking_Entity] = "KSA" and not Text.StartsWith([Coverage_Unit],"KSA") then "Others"
else
if [Booking_Entity] = "BSC" and Text.StartsWith([Coverage_Unit],"KSA") or Text.StartsWith([Coverage_Unit],"MGM") or Text.StartsWith([Coverage_Unit],"LEG") then "Others"
else
if [Booking_Entity] = "BSC" and not Text.StartsWith([Coverage_Unit],"KSA") then [Coverage_Unit]
else
if Text.StartsWith([Coverage_Unit],"MGM") then "Others"
else ""),
#"Changed Type1" = Table.TransformColumnTypes(#"Conditional Col - Booking and coverage unit",{{"Value", type number}})
in
#"Changed Type1"

[v-dineshya · Accepted solution]

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 · Accepted solution]

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

[SandoK]

Thank you v-dineshya for the solution!!!

[v-dineshya · Accepted solution]

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]

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]

Thank you MarkLaf for the response.

[SandoK]

Thanks @lbendlin . But i am not able to find the step which is causing this error.

[lbendlin]

You may want to consider refactoring the code a bit to make it easier to manage ( and easier to spot issues ).  Consider using List.Contains()  instead of these nested IFs. 

ID "numbers" don't need to be in number format to be useful. Some ID systems use a mix of letters and digits.

Since your data source is Excel, likely controlled by humans, you will have to spend extra effort in hardening the Power Query code, and have to expect these kinds of breakages anyway.

[SandoK]

Thank you lbendlin.