Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

[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
Zubair_Muhammad
Community Champion
Community Champion

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]

Regards
Zubair

Please try my custom visuals

View solution in original post

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

 

 

 


Regards
Zubair

Please try my custom visuals

View solution in original post

8 REPLIES 8
Zubair_Muhammad
Community Champion
Community Champion

@Anonymous 

 

Please see attached file's Query Editor as well

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

 

 

 


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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.


Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

Zubair_Muhammad
Community Champion
Community Champion

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]

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.