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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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
298
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
298
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,383
Messages
5,635,942
Members
416,889
Latest member
dhegs

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