M Code - change field names

JasL

Board Regular
Joined
Dec 16, 2002
Messages
79
Hi

I need to change a number of field Names in a DataTable. Dozens, and it may chnage going fwd, hence the need for automation.

I have Table (FieldNameChgTable) with the fields to change. Can anybody PLEASE help with the M-Code to loop through the FieldNameChgTable to update the DataTable.
I can do it with VBA, but I'm trying toget more skilled in M-Code - but not sure how to do this. :(

Example

Pre Change
DataTable
Field1, Field2, Field3, Field4
x, x, x, x,
x, x, x, x,

Changes
FieldNameChgTable
From, To
Field1, NewName1
Field4, NewName4

Post Change
DataTable
NewName1, Field2, Field3, NewName4
x, x, x, x,
x, x, x, x,

Thx for your time :)
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

JasL

Board Regular
Joined
Dec 16, 2002
Messages
79
Thx for help, but I couldnt get this to work....
= Table.RenameColumns(#"Pre Change",Table.ToRows(Changes))
Went with:

let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Renamed Columns" = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source), Table.ColumnNames(Table1)}) )
in
#"Renamed Columns"

Where
Table 1
a, b
x, x

Table 2
A1, B1
x, x

Code imports Table 2, then ReNames fields based on Table 1
Table 2 (imported)
a, b
x, x
 
Last edited:

macfuller

Active Member
Joined
Apr 30, 2014
Messages
270
Office Version
365
Platform
Windows
I think you'll need to work on the table schema rather than within the table itself. Sorry I haven't altered it to suit you but I hope this is quicker to get you started than to wait a couple of days for me to try a solution.

This code changes the column type based on search criteria. I hope if you have your list of before and after column names you can use much the same logic to swap names.

Code:
    SearchText = "Daily",
    SearchColumnType = "Any.Type",
    NewColumnType = "type text",


//    --------------------------------------------------------------
//    Change the column types based on column type
//    --------------------------------------------------------------

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


    GetTableSchema = Table.Schema(Source),


//    Match the type for a transformation.  Set to <> to change all others
    FilterRowsToMatchType = Table.SelectRows(GetTableSchema, each ([TypeName] = SearchColumnType)),
    GetFirstListColumn = Table.SelectColumns(  FilterRowsToMatchType,{List.First(Table.ColumnNames(FilterRowsToMatchType))}),
    ConvertToList = Table.ToList(GetFirstListColumn),
    CountOfChanges = List.Count(ConvertToList),
    TransformColumnsType = Table.TransformColumnTypes(ChangeColTypes, Table.ToRows(Table.FromColumns({ConvertToList, List.Repeat({type text}, CountOfChanges  )}))),
 

macfuller

Active Member
Joined
Apr 30, 2014
Messages
270
Office Version
365
Platform
Windows
Sorry, I made an incorrect assumption. What you posted should be fine. But you can certainly use the Table.Schema for other manipulations of column type etc if you need further changes.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,139
Messages
5,466,910
Members
406,506
Latest member
donwiss

This Week's Hot Topics

Top