Log In
Register
Home
February 1, 2019
Offline
Hi Guys,
i have table like here:
TableNameColumnTypesCustom
| TableTest | {"Col1",type text}, {"Col2",Int64.Type}, {"Col3",type logical} | [List] |
| Table3 | {"Col1",type text}, {"Col2",Int64.Type}, {"Col3", type number}, {"Col4", type logical} | [List] |
In List i have :
So as you can see For each Column Name i have String which is equivalent to "type text", "Int64.Type" etc.
I am generally interested only in Int64.Type and number.
So in case of TableTest i have Col1 - type text in List, Col2 - Int64.Type in list Col3 - type logical.
And now in result table i am trying:
let
Source = Excel.CurrentWorkbook(){[Name="TableTest"]}[Content],
Transformation = Table.TransformColumns(Source,{{"Col2", each try Int64.From(_) otherwise _}})
in
Transformation
So what you can see i am trying to transform "Col2" to type Int64.Type. It is working for one column perfectly.
But what i need is to address this to all columns and check if statements are fulfill.
What i need here is to have in this place : "each try Int64.From(_) otherwise _" statement which will check Each column type and apply specific if condition.
So if Col2 = "Int64.Type" ( from TableNameColumnTypesCustom[List] ) i should apply try Int64.From(_) otherwise _, if text do something, if number try number.from(_) otherwise _
How to achive this? I suppose list.accumulate can handle it but i do not know how.
Please help,
Jacek
November 8, 2013
Offline
What's wrong with the solution provided in the previous topic you have posted for the same problem? I cannot see any difference in the requirements.
February 1, 2019
Offline
Thank you Catalin,
i had diffent shape of tables,
i managed to do this:
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
Best wishes for you,
Jacek
1 Guest(s)
