cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marcp2 Frequent Visitor
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
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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting

View solution in original post

2 REPLIES 2
Super User II
Super User II

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


MCSA: BI Reporting

View solution in original post

marcp2 Frequent Visitor
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
        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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors