Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
I have a table in which a column has any type and it has text, numbers, decimals like 0.98 or < 15 days or 15%
I tried to create a custom column using below code but it is not working as I am trying to get.
Text.Select(Number.ToText([Monthly Target New]),{"0".."9","."})
Here is how the data looks
In these both cases, I need 2 and 15
Please help
Solved! Go to Solution.
Hi, @PowerOn_DP
You can modify your code to delete data that is not a number and decimal point, there are many ways.
Like this:
= Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Monthly Target New],{"0".."9","."}))
or
= Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Monthly Target New], List.Transform({1..45,47,58..126}, each Character.FromNumber(_))))
Reference:Text.Remove - PowerQuery M | Microsoft Docs
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @PowerOn_DP
You can modify your code to delete data that is not a number and decimal point, there are many ways.
Like this:
= Table.AddColumn(#"Changed Type", "Custom", each Text.Select([Monthly Target New],{"0".."9","."}))
or
= Table.AddColumn(#"Changed Type", "Custom", each Text.Remove([Monthly Target New], List.Transform({1..45,47,58..126}, each Character.FromNumber(_))))
Reference:Text.Remove - PowerQuery M | Microsoft Docs
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PowerOn_DP
You don't need coding to do it. Use replace value to do it in Power Query (Select column then right click). Then change the type of the column.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |