Change column type when row values are mixed

[jaryszek]

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

[Payeras_BI · Accepted solution]

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

[jaryszek]

thabk 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

[Payeras_BI]

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"

[jaryszek]

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,
Jacek

[Payeras_BI]

Hi @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.