February 1, 2019
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
February 1, 2019
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)