M Query: return the column position

Power Query
1

Is it possible to return the column position number with M code?

2

Hi @powerbideveloper,

Power Query uses an index that starts at 0 for columns. {0}.

This refers to the first column:
Table.ColumnNames(#”Changed Type”){0}

Could you explain what it is your are trying to do please, so I could help further.

1 Like
3

Hi @powerbideveloper

as @marcster_uk has suggested you can use the function Table.ColumnNames(MyTable){n} to return a column name by its position.

I would also check out Record.Field function which you can use to reference a column by its name.

As suggested please share some further details for your specific use case.

Regards,
H

4

@marcster_uk and @haroonali1000

I am aware of the code you mention but in that case the position is known. In my scenario, the position is unknown but the table and column name is known.

It is a bit hard to explain but I’ll see if I can do so later when I get a chance.

For now, just know that I don’t know the position which what I need.

5

@powerbideveloper,
You could create a list of column names and add an index?.

6

@marcster_uk is right. When you know the Query and Column name, this shouldn’t be difficult just wondering how you’ll use that position Index… anyway create a Blank Query

  1. Table.ColumnNames(YourQueryName)
  2. Turn it into a Table
  3. Add an Index from 0
    Table.AddIndexColumn(#“Converted to Table”, “Index”, 0, 1)
  4. Apply the filter on the required Column Name and you’ll have your answer…
    image
    image771Ă—149 7.09 KB

    That Value can also be returned in a separate Query, like below.
    image
1 Like
7

@Melissa, @marcster_uk, @haroonali1000

Adding an index is actually part of the code already but as you can see, the zero in brackets {0} is hardcoded. It needs to be dynamic based on the column name.

My thinking is that having the table (query) and column name should allow one to search the table and return the column position number where column name = “TheColumnName”.

In other programming languages, this requires very basic coding but in M Query it seems like it’s not really a simple thing, so I will have to rethink things and I will update everyone here, so please don’t close this question until I have an update.

8

@Melissa, @marcster_uk, @haroonali1000

Since I get the sense that there is no answer to the question of how to programmatically return the column position, I will have to change the question title.

So, instead of using the column position, how can I achieve the same thing using the column name?

For example:
`Distinct1 = GetTable{“Column 1”}[Distinct] - See attached file

NOTE: I know that the above snippet produces an error. It’s the idea I’m trying to convey.

Melissa, you are familiar with this sample that both you and Ankit contributed to in a previous question.

eDNA-MQuery_NewDistinctTable_V2.zip (74.9 KB)

9

Think I’m missing something vital here Mark and also still struggling to understand the requirement…
Would you be so kind to provide a clear example based on the provided PBIX of what it is you intent to do and what you’d expect to happen - in both scenarios so based on column position and -name?

Thanks for your patience!

10

@Melissa,

I managed to find a solution to return the column position:

Table.Schema(MyTable){[Name=“MyColumn”]}[Position]

The above code returns the position number of the column.

Mark

1 Like