Forum Discussion
Solved
Change column types based on rows and function created
Hi Guys, I have 2 tables, one with ColumnTypes: TableNameColumnTypes TableTest {"Col1",type text}, {"Col2",Int64.Type}, {"Col3",type logical} Table3 {"Col1",type text}, {"Col2",t...
thank you for taking the topic.
I managed to solve the issue usinglet InputTable = "TableTest", Source = Excel.CurrentWorkbook(){[Name=InputTable]}[Content], TableType = Table.SelectRows(Excel.CurrentWorkbook(){[Name="TableType"]}[Content], each [TableName] = InputTable), TableTypeList = Table.AddColumn(TableType, "Custom", each fnGetListOfOccurences([ColumnTypes],"{","}")), Transformation = List.Accumulate(List.Numbers(0,Table.ColumnCount(Source)), Source, (state, index) => Table.TransformColumns(state,{{Table.ColumnNames(Source){index}, each if TableTypeList[Custom]{0}{index}{1}="Int64.Type" or TableTypeList[Custom]{0}{index}{1}="type number" then try Int64.From(_) otherwise _ else _}})), in Transformation
Hi jaryszek ,
Sorry, I don't understand much. Is your sample data the data in the screenshot below? What is the expected result, please provide.
You can detect data type in power query:
https://docs.microsoft.com/en-us/power-query/data-types#data-type-detection
Or define a column data type:
https://docs.microsoft.com/en-us/power-query/data-types#how-to-define-a-column-data-type
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you for taking the topic.
I managed to solve the issue using
let
InputTable = "TableTest",
Source = Excel.CurrentWorkbook(){[Name=InputTable]}[Content],
TableType = Table.SelectRows(Excel.CurrentWorkbook(){[Name="TableType"]}[Content], each [TableName] = InputTable),
TableTypeList = Table.AddColumn(TableType, "Custom", each fnGetListOfOccurences([ColumnTypes],"{","}")),
Transformation = List.Accumulate(List.Numbers(0,Table.ColumnCount(Source)), Source, (state, index) => Table.TransformColumns(state,{{Table.ColumnNames(Source){index}, each if TableTypeList[Custom]{0}{index}{1}="Int64.Type" or TableTypeList[Custom]{0}{index}{1}="type number" then try Int64.From(_) otherwise _ else _}})),
in
Transformation