cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlejandroG Regular Visitor
Regular Visitor

[Power Query] Check which column names equal a [Column] for a conditional column

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:

example3.PNG

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]:

example4.PNG

Can this be done?

 

Best regards.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: [Power Query] Check which column names equal a [Column] for a conditional column

Hi @AlejandroG 

 

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]
Highlighted
Super User
Super User

Re: [Power Query] Check which column names equal a [Column] for a conditional column

Sure@AlejandroG

 

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

 

 

 

8 REPLIES 8
AlejandroG Regular Visitor
Regular Visitor

Re: [Power Query] Check which column names equal a [Column] for a conditional column

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)

 

Super User
Super User

Re: [Power Query] Check which column names equal a [Column] for a conditional column

Hi @AlejandroG 

 

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]
Super User
Super User

Re: [Power Query] Check which column names equal a [Column] for a conditional column

@AlejandroG 

 

Please see attached file's Query Editor as well

 

 

AlejandroG Regular Visitor
Regular Visitor

Re: [Power Query] Check which column names equal a [Column] for a conditional column

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.

Highlighted
Super User
Super User

Re: [Power Query] Check which column names equal a [Column] for a conditional column

Sure@AlejandroG

 

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

 

 

 

AlejandroG Regular Visitor
Regular Visitor

Re: [Power Query] Check which column names equal a [Column] for a conditional column

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?

Super User
Super User

Re: [Power Query] Check which column names equal a [Column] for a conditional column

HI @AlejandroG 

 

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.

AlejandroG Regular Visitor
Regular Visitor

Re: [Power Query] Check which column names equal a [Column] for a conditional column

That makes sense, thanks a lot for your insights @Zubair_Muhammad!