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 :)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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
307
Office Version
  1. 365
Platform
  1. 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
307
Office Version
  1. 365
Platform
  1. 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,956
Messages
5,834,579
Members
430,298
Latest member
Stanles

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
Top