Change column types based on rows and function created

[jaryszek]

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",type Int64.Type}, {"Col3", type number}, {"Col4", type logical}

And SourceTable - "TestTable":

Col1Col2Col3

55	006	TRUE
66	SomeString	FALSE

and now i have 3 functions.

First one is to "getListOfOccurences" to get specific types for each column

Second one "fnTypes" to replace string to specific Type value. 

It is just specyfing what column Type we have - converting String to Type. 

And last one : fnGetListOfTypes it is just a filter to get proper table:

and  the result of all these functions i am getting table :

TableNameColumnTypesCustom

TableTest	{"Col1",type text}, {"Col2",Int64.Type}, {"Col3",type logical}	[List]
Table3	{"Col1",type text}, {"Col2",type Int64.Type}, {"Col3", type number}, {"Col4", type logical}	[List]

where i have all types within list for each table. 

And now as a result query where i am using dynamic Table.TransformColumnTypes function:

let Source = Excel.CurrentWorkbook(){[Name="TableTest"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,fnGetListOfTypes("TableTest")) in #"Changed Type"

I am getting:

error in second line because i am trying to change String to Number! 

I tried to use pattern:

= Table.TransformColumnTypes(Source,each try Int64.From(_) otherwise fnGetListOfTypes("TableTest"))

Maybe i am making this too complicated. I should just replace "006" to 6 as use it as any. But still i would have to know which column i should apply change. Like check each column if Type = number then remove leading zeros. 

but it is failing.Please help,
Best Wishes,
Jacek

[v-stephen-msft]

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.

[jaryszek · Accepted solution]

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

[jaryszek]

Please find Excel file from my google disc:

https://drive.google.com/drive/folders/13dy4nZIgfx6VPJHDwil5pky3ECQNnc4L?usp=sharing