cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Access to a column content using the number of the column, not its name

Hi

I try to compare the content ot two columns. It is easy when you know the name of the columns you have to compare.

If I have a column A and a Column B, I do

IF A<B THEN True ELSE False for example

Bur in my case I have a lot of pair of columns to test and I need to iterate with the number of the column

So I need somethin like that

IF Col(0)<Col(300) THEN True ELSE False

I found the way to iterate with a recursive function but I don't know how to access the content of the Column number X

Here is the function I tried to do. I have always False whatever the content of columns compared :

(Table as table, Index as number, NumCols as number) as table =>
let
"T_"&Table.ColumnNames(Table){Index},
each if Table.ColumnNames(Table){Index}= Table.ColumnNames(Table){Index+289} then true else false),
in

Could you help me ?

Regards

Marc

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Access to a column content using the number of the column, not its name

This formula will compare column #4 to column #5 (it indexes at zero, so the first column is 0) and return true or false.

`if Record.Field(_, Table.ColumnNames(#"Changed Type"){3}) > Record.Field(_, Table.ColumnNames(#"Changed Type"){4}) then true else false`

The table name #"Changed Type" is the table returned in the previous step of my sample query I did to test this. Just reference your previous step. It might be just Source if it is the first step after the table is imported.

2 REPLIES 2
New Contributor

## Re: Access to a column content using the number of the column, not its name

This formula will compare column #4 to column #5 (it indexes at zero, so the first column is 0) and return true or false.

`if Record.Field(_, Table.ColumnNames(#"Changed Type"){3}) > Record.Field(_, Table.ColumnNames(#"Changed Type"){4}) then true else false`

The table name #"Changed Type" is the table returned in the previous step of my sample query I did to test this. Just reference your previous step. It might be just Source if it is the first step after the table is imported.

Frequent Visitor

## Re: Access to a column content using the number of the column, not its name

Hi edhans

That's great, it's outstanding what we can do with a few lines of M code.

Thank you very much !!

So, this is the final function code including your help

```(Table as table, Index as number, NumCols as number) as table =>
let
"T_"&Table.ColumnNames(Table){Index},
each if Record.Field(_, Table.ColumnNames(Table){Index}) > Record.Field(_, Table.ColumnNames(Table){Index+289}) then true else false),
in

Kind regards

Marc

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 115 members 1,544 guests
Recent signins: