Log In
Register
Home
February 1, 2019
Offline
hi Guys,
i am using this code to change all columns of table to type "text":
Table2 query:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = List.Accumulate(Table.ColumnNames(Source), Source, (state, current) => try Table.TransformColumnTypes(state,{{current, type text}}) otherwise state)
in
#"Changed Type"
But what i have to is to provide exactly from another table column types and use it in function to get proper columns types in Table2.
ColumnTypes table:
TableNameColNameColumnType
| Table2 | Col1 | text |
| Table2 | Col2 | Int64.Type |
| Table2 | Col3 | number |
So from this table i have to get that Col1 is text, Col2 is Int64.Type and apply into my query for Table2.
How can i achieve this ?
Thank you for help,
Jacek
See Answer
November 8, 2013
Offline
Build a custom function to get the type. If the name of the new function is GetType, replace:
{current, type text}
with:
{current, type GetType(current)}
(pass the current column name to the new function, that should return the type from your types table.
February 1, 2019
Offline
Thank you.
So in this function just filtering table to get specific row?
I have to get just an exact value , not list ?
Best Wishes,
Jacek
November 8, 2013
Offline
Yes, filter to get to the row you need, then extract the value from the column you want.
Post your function here if you get stuck.
February 1, 2019
Offline
Hello,
i am getting back with function:, something like:
let
TabName = "Table2",
ColName = "Col2",
Source = ColumnTypes,
#"Filter" = Table.SelectRows(Source, each ([TableName] = TabName and [ColumnName]= "Name")){0}[ColumnType]
in
#"Filter"
the issue is that now i have to do something similar but from string:
{"DiskSize",Int64.Type}, {"DiskSizeGiB",type text} - i have exact strings in table which i should use as columns.
Could i read the string and put in ChangedType statement somehow?
Best,
Jacek
February 1, 2019
Offline
So Table4:
TableNameColumnTypes
| Table2 | {"Col1",type text}, {"Col2",type Int64.Type}, {"Col3", type number} |
In attachment please find workbook example.
Best,
Jacek
November 8, 2013
Offline
See file attached for an example.
Answers Post
February 1, 2019
Offline
You are thoe boss!!
Thank you,
Jacek
New Member
November 28, 2022
Offline
What do I need to change in solution of #7 to make it work for the other types?
| #"Changed Type" = Table.TransformColumnTypes(Source,{ |
| {"Col_a", type number}, |
| {"Col_b", Currency.Type}, |
| {"Col_c", Int64.Type}, |
| {"Col_d", Percentage.Type}, |
| {"Col_e", type datetime}, |
| {"Col_f", type date}, |
| {"Col_g", type time}, |
| {"Col_h", type datetimezone}, |
| {"Col_i", type duration}, |
| {"Col_j", type text}, |
| {"Col_k", type logical}, |
| {"Col_l", type binary}}) |
1 Guest(s)
