cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
edhans New Contributor
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
edhans New Contributor
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.

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 !! Smiley Happy

 

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
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 115 members 1,544 guests
Please welcome our newest community members: