Posted on in Power Query

#PowerQuery – Change the Column name to the index of the column

Inspired by Ruth Pozuelo from Curbal latest video – https://www.youtube.com/watch?v=jlogBrcYZwc&t=2s where she shows an excellent tip on how to change column headers to upper or proper case –

And the trick is to use the second argument in the Table.TransformColumnNames – and for instance use Text.Upper to sett all names to capitals.


You can also use

Text.Lower

Text.Proper

I decided to check if I could use some of the other text functions to do even more 🙂

So why not see if we could give each column the name of the index number they have in the query.

And it turns out that it’s possible

If you add the following step

= Table.TransformColumnNames(Custom3, each “”)

‘

The each statement will to the renaming for each column and name it to an empty string – and if you have more than one column with the same name it will get a index number added as a suffix – so column 2 will be name empty string + 1 and so on.

In order to rename the columns to start with 1 instead of an empty string we can use the TransformColumnNames once more but this time to add 1 to each column names – but as column names is text we need to convert it to a value and back to text again

= Table.TransformColumnNames(#”Custom1″, each if _ = “” then “1” else Text.From(Value.FromText(_)+1))

And now we can refer to cells in our table using coordinates

Hope you find this useful and happy querying 🙂

13 thoughts on “#PowerQuery – Change the Column name to the index of the column”

  1. Hi,

    thanks for great post.

    when I try to implement it

    HeaderIndex1 = Table.TransformColumnNames ( Source , each
    if _ = “” then “1” else Text.From(Value.FromText (_)+1))

    I got an error :

    Expression.Error: We cannot apply operator + to types Text and Number.
    Details:
    Operator=+
    Left=Date
    Right=1

    Did you see something like that.

    Thanks in advanced.
    Nir

  3. Dear Arik,

    Thanks for your help.
    Please find below M code ,
    The last step is your magic 🙂

    Thanks in advance,
    Nir

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“XVC7agNBDPyXrY1Oo31JbVKGNGkPFwmYYDgSCPb/R/ZxPq2rFczOc54T+gSehNHTIZViBPPj5bws6XiYk8gE2eBcqaq/b6efO+gI6waCQb368Xr9u6xcBG4Dk4ofH79fd5Qxse3KTKVEY2jQdjD6cgvUSvlGfP+8rK6O7IWgRizPofCwLaAayZIDWZHXRo/IFgrVQtyjMiyk6kIYxuAeuL5FDX2kxshm1PLQSIKwlE5FhswDW4jHHXucmQk6rBG5OTcCbtLn7+tp2Srv9GaZmj59iL3U3S3gx38=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Sale = _t, SalesPerson = _t]),

    HeaderUpper = Table.TransformColumnNames ( Source , Text.Upper),
    HeaderLower = Table.TransformColumnNames ( Source , Text.Lower),
    HeaderProper = Table.TransformColumnNames ( Source , Text.Proper),
    HeaderIndex = Table.TransformColumnNames ( Source , each “”),
    HeaderIndexNotWorking = Table.TransformColumnNames ( Source , each
    if _ = “” then “1” else Text.From(Value.FromText (_)+1))
    in
    HeaderIndexNotWorking

    1. Hi Nir

      In your step HeaderIndexNotWorking – you are using the Source step as the table – it should be HeaderIndex

      = Table.TransformColumnNames ( HeaderIndex , each
      if _ = “” then “1” else Text.From(Value.FromText (_)+1))

      Best Regards
      Erik

  5. Hi Erik,
    It’s simple enough to create the index column names in one step e.g.
    RenamedColumns = Table.TransformColumnNames(, each Number.ToText(List.PositionOf(Table.ColumnNames(), _) + 1))
    However, I cannot think of an application where replacing the context of a named column with an nameless index would be useful. Could you provide any usage examples?

    1. Hi very nice way to do the same … usage examples might be hard to find but I have used it a case where the data came with headers in different local languages and I neede to refer to the column index .. and using the list.positionOf probably also could hve been the solution

  7. Hi Erik,
    i have multiple columns and each column name stars with “Cost Center.” word and i want to replace that word with “CC.” Is this possible using M-query? Kindly provide the solution.
    Thanks.

    1. Hi Abid

      You can do it like this

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WKkktLlHSAVMKRjCGiVJsLAA=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#”Cost Center 1″ = _t, #”Cost Center 2″ = _t, #”Cost Center 3″ = _t]),
      NewNames = Table.ColumnNames(Source),
      #”Converted to Table” = Table.FromList(NewNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #”Added Custom” = Table.AddColumn(#”Converted to Table”, “Custom”, each Text.Replace([Column1], “Cost Center “, “CC “)),
      #”Transposed Table” = Table.ToColumns(Table.Transpose(#”Added Custom”)),
      Result = Table.RenameColumns(Source, #”Transposed Table”)
      in
      Result

      Best Regards
      Erik

Leave a comment