Change column type from a list of types?

macfuller

Active Member
Joined
Apr 30, 2014
Messages
275
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

macfuller

Active Member
Joined
Apr 30, 2014
Messages
275
Office Version
  1. 365
Platform
  1. Windows
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.
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
275
Office Version
  1. 365
Platform
  1. Windows
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
 

Ozeroth

Active Member
Joined
Dec 14, 2013
Messages
264
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,360
Messages
5,528,233
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top