Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Good Morning,
I have this table. As you can see in Result column sometimes I have text sometimes but also I have numbers, either Whole Numbers or Decimal Numbers. I'm wondering How can I change the data type of the column depending the value.
In fact, I'm trying to change in the Main Menu
But I'm getting this 2 messages
I tried many different things but it doesn't work.
This Result column I'm using sometimes for trending chart so I need to take the numbers as Data type Number or Decimal.
I hope someone can help me.
Thanks a lot in advance
Solved! Go to Solution.
Hi @RWRW
I noticed the changes @tharunkumarRTK and @ryan_mayu suggested but noticed that for both lines you are using "Changed Type" as the first argument.
The first argument for the second column added should refer to your previous step. If you refer to an earlier step, the steps in between will be missed.
Change the first argument of your second column (your text column) to refer to the step name for the first column (your numeric column).
In my example when creating the numeric column, my step name ended up as "Custom1" and the code is as follows:
= Table.AddColumn( #"Changed Type", "Numeric Result", each try Number.From([Result]) otherwise null )
For my text column, i used the following:
= Table.AddColumn( Custom1, "Text Result", each if [Numeric Result] = null then [Result] else null )
Notiice the change in the first arguments?
I hope this helps.
Sorry this screenshot
we can only set up one data type for one column. if the column contains both text and number, the data type can only be TEXT.
you can try to create a new column in PQ to retrieve numbers.
=try Number.FromText([Result]) otherwise null
Proud to be a Super User!
@RWRW
I would suggest you to split your result column into two different columns based on their type and proceed accordingly. You can split it by following this syntax:
= Table.AddColumn(#"Added Custom", "Number", each if Value.Type([Result]) = Number.Type then [Result] else null, type number)
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
Hi,
I tried and all values are null and I don't know if I'm doing something wrong
Thanks
I got it for Numbers
What about for text?
Thanks a lot
pls try
=if [Numeirc Result] is null then [result] else null
Proud to be a Super User!
= Table.AddColumn(#"Changed Type", "Text", each if Value.Type([Result]) = Text.Type then [Result] else null,type text)
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
Hi again,
Something is incorrect? Copy and paste...
Thanks
Your column name is 'Result', with upper case R
Yes yes I realized, I already changed and still not working
I understand the problem, your result column data type should be 'Any' for the formula to work, if it is text then you can follow this syntax
= Table.AddColumn(#"Changed Type1", "Custom", each try Number.From([Result]) otherwise null)
If the post helps please give a thumbs up
If it solves your issue, please accept it as the solution to help the other members find it more quickly.
Tharun
Hi Again,
For Number is ok
But For text not, It's giving all the values, It should be null for numeric values and it's not.
I mean Text Result COlumn should be
null
null
Approved
Approved
Failed
Thanks a lot again, hope you can help me
@RWRW
Please share your file, I will add those two columns and can give it to you.
Hi,
How Can I share the file?
Anyway, if you can tell me the function I will add no worries.
For Numeric Column the formula is already ok. I only need when is text.
Thanks
Table.AddColumn(#"Changed Type", "Text", each if Value.Type([Result]) <> Number.Type then [Result] else null,type text)
Hi again,
Already changed and it doesn't work
The first two in Text Result Column should be null and it's not
Thanks
Hi @RWRW
I noticed the changes @tharunkumarRTK and @ryan_mayu suggested but noticed that for both lines you are using "Changed Type" as the first argument.
The first argument for the second column added should refer to your previous step. If you refer to an earlier step, the steps in between will be missed.
Change the first argument of your second column (your text column) to refer to the step name for the first column (your numeric column).
In my example when creating the numeric column, my step name ended up as "Custom1" and the code is as follows:
= Table.AddColumn( #"Changed Type", "Numeric Result", each try Number.From([Result]) otherwise null )
For my text column, i used the following:
= Table.AddColumn( Custom1, "Text Result", each if [Numeric Result] = null then [Result] else null )
Notiice the change in the first arguments?
I hope this helps.
Hi,
You are right, after refering Numeric column it works perfectly.
I would like to send thanks all of you to spend time with my issue.
I'm really appreciated
Thanks again
not sure, why these formulas are not working for you. I am able to run them
please upload it to any storage accounts like google dirve and share the link after granting access.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
183 | |
111 | |
105 | |
77 | |
70 |