Search code examples
powerquerym

Create conditional Table.TransformColumnType in powerquery


I am trying to convert columns to numeric. If TransformColumnTypes causes an error, I want to keep it text. Something like this:

#"Changed Type" = try  Table.TransformColumnTypes(CombineTables,List.Transform(sTranCol, each {_, type number})), otherwise  Table.TransformColumnTypes(CombineTables,List.Transform(sTranCol, each {_, type number})),

Obviously this doesn't work. sTranCol is the list of columns to covert to numeric. It is dynamically created and isn't static. I don't care if it puts error in the cell but transposing with errors in the cells is causing query to abort.


Solution

  • The M Code methods I've seen to detect data type of a column consist of sampling the data and determining the type. This seems messy.

    But perhaps an alternative might be type the columns as numeric, and then replace the error values with something that won't cause a problem when transposing.

    Here is some sample code to replace errors with null, but you could replace with anything null or numeric:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTJUitWJBpI6SsZglhGQZQ5mVQBZiWCWKZCVBGaZA1kVEB0ghYYmSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
         #"Changed Type" = Table.TransformColumnTypes(Source, 
            List.Transform(Table.ColumnNames(Source), each {_,type nullable number})),
        
        nullList = List.Transform(Table.ColumnNames(#"Changed Type"), each {_, null}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", nullList)
    in
        #"Replaced Errors"
    

    Source
    enter image description here

    Changed Type
    enter image description here

    Replaced Errors
    enter image description here

    Edit: Add M Code to set column types depending on if all numeric

    let
        Source = Excel.CurrentWorkbook(){[Name="Table37"]}[Content],
    
    //check data type
    //if all numbers set to number, else any
    
    colTypes = List.Accumulate(Table.ColumnNames(Source),
        {},
        (state,current)=> List.Combine({state,
        if List.IsEmpty(
                List.RemoveMatchingItems(
                    List.Transform(Table.Column(Source,current), each Value.Type(_)), 
                    {type number})) 
        then {{current, type number}} 
        else {{current, type any}}})),
    
        #"Changed Type" = Table.TransformColumnTypes(Source,colTypes)
    
    in
        #"Changed Type"
    

    Source
    enter image description here

    Changed Type
    enter image description here