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
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.
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
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.
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
So Table4:
TableNameColumnTypes
| Table2 | {"Col1",type text}, {"Col2",type Int64.Type}, {"Col3", type number} |
In attachment please find workbook example.
Best,
Jacek
See file attached for an example.
You are thoe boss!!
Thank you,
Jacek
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}}) |
What other types you have in mind?
Should be easy to add new types in the list you mentioned, with any types you need.
