Forum Discussion

jaryszek's avatar
jaryszek
Super User
4 years ago
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",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

Need Help
  • jaryszek's avatar
    jaryszek
    4 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      jaryszek
      Super User
      4 years ago

      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