Change column type from a list of types?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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
Source Column NameRemoveNew Column NameNew Column Type
Field 1type text
Field 2Y
Field 3Unit Pricetype 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.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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.
 
Upvote 0
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.

Old NameRemove ColNew NameNew TypeConvert null to Zero
Item DescriptionNuVia Nametype text
Item IDPSFT IDtype text
UOMUnit of Measuretype text
CompartmntBintype text
Par Replenishment OptionIn Housetype text
Min QtyInt64.Type
Avg Daily Usagetype number
Days on HandDoHInt64.Type
Yesterday Vendor OrdersYY
Yesterday Received QtyYY
Yesterday MSR OrdersYY
History Order MSR CountMSR HistoryInt64.Type
History Order Count No MSROrder HistoryInt64.Type
Spin Ratetype number
Spin Categorytype text

<tbody>
</tbody>

Code:
/*      ---  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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top