Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThis 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |