Forum Discussion
Change column type when row values are mixed
Hi Guys,
my table is:
Col1Col2Col3
| AB | 05 | BRE |
| CD | SomeString | ACB |
as you can see Col2 has text but what i want is to change "05" to 5 because as a goal it will be integer.
How can i do this?
I can not change Column Type because "SomeString" will throw the error.
I was thinking to unpivoting table like:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "ColumnType", each Value.Type([Value]))
in
#"Added Custom"
but still Value.Type is treatead as text.
In later parts i am changing "SomeString" to integer but i would like to just get rid of 05 on the beginning.
How can i do this?
IT is possible to change only row data type?
Best,
Jacek
Hi again jaryszek ,
I was now thinking that you won't only have an integer in your column but many.
Please try this other option:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyMAUSTkGuSrE60UrOLkBOcH5uanBJUWZeOpDj6OwElnF1A3IMDYGEe4ijUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), Transformation = Table.TransformColumns(Source,{{"Column2", each try Int64.From(_) otherwise _}}) in Transformation
5 Replies
Hi jaryszek ,
You could replace it this way:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyMAUSTkGuSrE60UrOLkBOcH5uanBJUWZeOpDj6OykFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"05",5,Replacer.ReplaceValue,{"Column2"}) in #"Replaced Value"Thank you J. Payeras
Ok but before i will replace i would like to check if 05 is string or integer type. Assuming that this is Integer (it can be from another table) i would like to change data type. If this is string i would like to know it and not change type.
It is not possible for rows to check data types?Best,
JacekHi jaryszek,
Please check my second answer to this thread.
With that I think you would get what you are looking for, checking row by row to keep the strings as they are and only convert the integers.
Hi again jaryszek ,
I was now thinking that you won't only have an integer in your column but many.
Please try this other option:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyMAUSTkGuSrE60UrOLkBOcH5uanBJUWZeOpDj6OwElnF1A3IMDYGEe4ijUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]), Transformation = Table.TransformColumns(Source,{{"Column2", each try Int64.From(_) otherwise _}}) in Transformationthabk you very much!
try Int64.From(_) otherwise _How this will work?
What does "_" mean ?
And what if i want to check Column5 also, can i create getType function to check ? Assume that i have also logical types in the table.
So i want to check every column and check if this is potential type.
If Int64.type then replace, if logical change to logicalm, if string change to string.
Thank you,
Jacek
