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.
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"
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"