I'm creating a parameter table to set values from outside the query, so that when changes happen to my source files I don't have to trigger the query to re-run when I want to add or delete a column. Source data is in weekly (Monday) batch files - if I know on Wednesday I'll have a new column but don't want to re-run the query until Monday I have to wait until then to make all query modifications. Instead, if I have a table of values, I can modify the table and the query will look for the changes when it runs Monday 6am.
Sample table in spreadsheet = tblMetaData
<tbody>
</tbody>
I am able to manipulate my source file to remove columns with "Y" in Remove and to rename the original column name with the New Column Name. But I can't figure out how to change the column types.
In the case above I want to change the types of columns 1 and 3 in the source data. As with the other options I can filter and generate the list of fields to change and the column types to change them to, getting {"Field 1", "type text"} and {"Field 3", "type number"}
but
ChangeColTypes = Table.TransformColumnTypes(PriorStep, List.Zip({ListColNamesToChange, ListNewChangeTypeValuess})),
returns
Expression.Error: We cannot convert the value "type text" to type Type.
Details:
Value=type text
Type=Type
The Online explanation for the TransformColumnTypes function shows text values for the Type parameters so I'm stuck at this point. Any help will be appreciated.
Sample table in spreadsheet = tblMetaData
Source Column Name | Remove | New Column Name | New Column Type |
Field 1 | type text | ||
Field 2 | Y | ||
Field 3 | Unit Price | type number | |
etc |
<tbody>
</tbody>
I am able to manipulate my source file to remove columns with "Y" in Remove and to rename the original column name with the New Column Name. But I can't figure out how to change the column types.
In the case above I want to change the types of columns 1 and 3 in the source data. As with the other options I can filter and generate the list of fields to change and the column types to change them to, getting {"Field 1", "type text"} and {"Field 3", "type number"}
but
ChangeColTypes = Table.TransformColumnTypes(PriorStep, List.Zip({ListColNamesToChange, ListNewChangeTypeValuess})),
returns
Expression.Error: We cannot convert the value "type text" to type Type.
Details:
Value=type text
Type=Type
The Online explanation for the TransformColumnTypes function shows text values for the Type parameters so I'm stuck at this point. Any help will be appreciated.