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

[marcp2]

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
ColumnsAdded_=Table.AddColumn(Table,
"T_"&Table.ColumnNames(Table){Index},
each if Table.ColumnNames(Table){Index}= Table.ColumnNames(Table){Index+289} then true else false),
ColumnsAdded = if (Index < NumCols) then @AddColumn( ColumnsAdded_, Index + 1, NumCols ) else Table
in
ColumnsAdded

Could you help me ?

Thanks for your help

Regards

Marc

[edhans · Accepted solution]

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.

[marcp2]

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
        ColumnsAdded_=Table.AddColumn(Table,
                                     "T_"&Table.ColumnNames(Table){Index},
                                       each if Record.Field(_, Table.ColumnNames(Table){Index}) > Record.Field(_, Table.ColumnNames(Table){Index+289}) then true else false),
        ColumnsAdded = if (Index < NumCols) then @AddColumn( ColumnsAdded_, Index + 1, NumCols ) else Table
    in
        ColumnsAdded

Kind regards

Marc