Forum Discussion
[Data.Format]Error we couldn't convert to number
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 RegardsHi 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
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.
Thank you lbendlin.