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 can´t figure out this. I´ve looked in the forum but didn´t found an answer either.
Given a table like this:
I´d like to check which [Year] equals to a column name in the table, and then multiply [Value] by that [Column] found.
For example, in the first row [Year] = 2017, so I´d like the conditional column to output the value in column [2017] by [Value]:
Can this be done?
Best regards.
Solved! Go to Solution.
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]
Sure@Anonymous
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
@Anonymous
Please see attached file's Query Editor as well
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.
Sure@Anonymous
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.
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]
I´d be something like:
= Table.AddColumn(#"Previous Step", "Calculated_Column", each if List.Contains(Table.ColumnNames(#"Previous Step"), [Year]) then [Capture that column]*[Value] else 0)
I´m trying to use List.Buffer for the [Capture that column] part but not getting there (yet).
I´ve also tried getting the name of the column using List.RemoveMatchingItems two times, the idea is like this:
List.RemoveMatchingItems ({"Category", "Value", "Year", "2017", "2018"}, {"2017"}) equals {"Category", "Value", "Year", "2018"}
So you could do:
List.RemoveMatchingItems ({"Category", "Value", "Year", "2017", "2018"}, List.RemoveMatchingItems ({"Category", "Value", "Year", "2017", "2018"}, {"2017"}) equals
{"2017"}
I´d just need to use that one item list as a record table reference, something like:
= Table.AddColumn(#"Previous Step", "Calculated_Column", each if List.Contains(Table.ColumnNames(#"Previous Step"), [Year]) then [Text.Combine(
List.RemoveMatchingItems(Table.ColumnNames(#"Previous Step"), List.RemoveMatchingItems(Table.ColumnNames(#"Previous Step"),
[Year])))]
*[Value]
else 0)
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 |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |