Change column type from a list of types?

[macfuller]

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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Source Column Name[/TD]
[TD]Remove[/TD]
[TD]New Column Name[/TD]
[TD]New Column Type[/TD]
[/TR]
[TR]
[TD]Field 1[/TD]
[TD][/TD]
[TD][/TD]
[TD]type text[/TD]
[/TR]
[TR]
[TD]Field 2[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Field 3[/TD]
[TD][/TD]
[TD]Unit Price[/TD]
[TD]type number[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[macfuller]

Upon further review I can convert the text to Type by adding a line

ConvertedTypeVals = Table.TransformColumns(FilterColsToChangeType, {{Table.ColumnNames(Rename){3}, Expression.Evaluate}})

and then the ChangeColTypes line in my first post works. Hooray!

But...

It appears that Expression.Evaluate only takes primitive data types? It will do text, number, and date but I've tried every combination of possible integer text and it won't go through.

 

[Ozeroth]

Hi macfuller

Try changing
Expression.Evaluate
to
each Expression.Evaluate(_, #shared )

Adding #shared allows Expression.Evaluate to access functions/types etc from the global environment, so values like "Int64.Type" can be interpreted correctly.This post explains what's going on
https://blog.crossjoin.co.uk/2015/02/06/expression-evaluate-in-power-querym/

 

[macfuller]

This does the trick, thanks. Now I can go on to marking whether the column should remove errors, convert blanks to null (or vice versa) and so on.

The #shared link you provided is too abstruse for me without a lot of background, though I'm grateful you were able to boil it down for me! Are there any helpful books you could recommend? I loved M is for Data Monkey but what's the next level?

For anyone else viewing this thread and wanting to do something similar, here's the simple table "tblReplace" with the values I'm seeking to use on the source file, then the PQ code. I'll change it to a function returning a table when I'm done.

[TABLE="width: 540"]
<tbody>[TR]
[TD]Old Name[/TD]
[TD]Remove Col[/TD]
[TD]New Name[/TD]
[TD]New Type[/TD]
[TD]Convert null to Zero[/TD]
[/TR]
[TR]
[TD]Item Description[/TD]
[TD][/TD]
[TD]NuVia Name[/TD]
[TD]type text[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item ID[/TD]
[TD][/TD]
[TD]PSFT ID[/TD]
[TD]type text[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UOM[/TD]
[TD][/TD]
[TD]Unit of Measure[/TD]
[TD]type text[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Compartmnt[/TD]
[TD][/TD]
[TD]Bin[/TD]
[TD]type text[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Par Replenishment Option[/TD]
[TD][/TD]
[TD]In House[/TD]
[TD]type text[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Min Qty[/TD]
[TD][/TD]
[TD][/TD]
[TD]Int64.Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Avg Daily Usage[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]type number[/TD]
[/TR]
[TR]
[TD]Days on Hand[/TD]
[TD][/TD]
[TD]DoH[/TD]
[TD]Int64.Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Yesterday Vendor Orders[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Yesterday Received Qty[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]Yesterday MSR Orders[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]History Order MSR Count[/TD]
[TD][/TD]
[TD]MSR History[/TD]
[TD]Int64.Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]History Order Count No MSR[/TD]
[TD][/TD]
[TD]Order History[/TD]
[TD]Int64.Type[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Spin Rate[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]type number[/TD]
[/TR]
[TR]
[TD]Spin Category[/TD]
[TD][/TD]
[TD][/TD]
[TD]type text[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

/*      ---  SetMetaDataFromTable  ---*/


let
    Source = Excel.CurrentWorkbook(){[Name="tblCart"]}[Content],
    Rename = Excel.CurrentWorkbook() { [Name = "tblReplace"]}[Content],


//    Identify the unwanted columns first and then the remainder


    FilterColsToDelete = Table.SelectRows(Rename, each ([Remove Col] = "Y")),
    ListZapVals = Table.Column(FilterColsToDelete, Table.ColumnNames(Rename){0} ),
    FilterColsToSave = Table.SelectRows(Rename, each ([Remove Col] is null)),


//    Identify the columns to rename and create the lists of old and new names


    FilterColsToRename = Table.SelectRows(FilterColsToSave, each ([New Name] <> null)),
    ListOldNameVals = Table.Column(FilterColsToRename, Table.ColumnNames(Rename){0} ),
    ListNewNameVals = Table.Column(FilterColsToRename, Table.ColumnNames(Rename){2} ),




//    Identify the columns to change the type.  We will do this based on the old names and first filter where there is a type listed


    FilterColsToChangeType = Table.SelectRows(FilterColsToSave, each ([New Type] <> null)),
    ListChangeTypeNameVals = Table.Column(FilterColsToChangeType, Table.ColumnNames(Rename){0} ),
    RawTypeList = Table.Column(FilterColsToChangeType, Table.ColumnNames(Rename){3} ),


    ConvertedRawList = List.Transform(RawTypeList, each Expression.Evaluate( _, [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=shared]#shared[/URL] ) ),


//    --  Implement actions for the columns  --
//    Delete the unwanted columns


    RemoveCol = Table.RemoveColumns(Source,ListZapVals),


//    Change the column types to the new values


    ChangeColTypes = Table.TransformColumnTypes(RemoveCol, List.Zip({ListChangeTypeNameVals, ConvertedRawList})),


//    Finally, rename the columns with the new name


    RenameCols = Table.RenameColumns(ChangeColTypes, List.Zip({ListOldNameVals, ListNewNameVals}))


in


    RenameCols

 

[Ozeroth]

That's good news
I also have Chris Webb's book, but that is probably a similar level to M is for Data Monkey.
I hear Gil Raviv's book is quite good. It covers some specific analytical examples which sounds like a good approach:
https://www.amazon.com/dp/1509307958