Forum Discussion
[Power Query] Check which column names equal a [Column] for a conditional column
Hi Anonymous
First add an Index Column Starting from 0
Then you can use this Custom Column from Query Editor
=let mycolumn=Text.From([Year]) in Table.Column(#"Added Index",mycolumn){[Index]}*[Value]SureAnonymous
Let me tell you first that there is an even easier solution which doesn't even require to Add an Index Column.
Sorry it didn't occur to me first
Just Add this custom column. It should give you desired result
=Record.Field(_,Text.From([Year]))*[Value]
_ gets you the current row record. Second argument is the fieldname from which you want to fetch the value
https://docs.microsoft.com/en-us/powerquery-m/record-field
Thanks a lot for the help Zubair_Muhammad, that works great!
Would you mind to explain a bit what is going on in your expression? i.e. does the table need to be sort by Category and Year?
Best regards.
SureAnonymous
Let me tell you first that there is an even easier solution which doesn't even require to Add an Index Column.
Sorry it didn't occur to me first
Just Add this custom column. It should give you desired result
=Record.Field(_,Text.From([Year]))*[Value]
_ gets you the current row record. Second argument is the fieldname from which you want to fetch the value
https://docs.microsoft.com/en-us/powerquery-m/record-field
I did not know of the Record.Field function, it is really helpful!
Zubair_Muhammad do you know if there is any difference in performance between your two solutions?
HI Anonymous
Record.Field should be much faster than Table.Column because
In Record.Field we can immediately access the current row/record using "_"
Whereas
In Table.Column we have to take the Table (Added Index) from previous Step and then use Index reference to get current row/record.
That makes sense, thanks a lot for your insights Zubair_Muhammad!