Reference column by variable or value

[Primaryorange]

Hello!

I know this has been answered in the past as "not yet doable", but I'm curious if anyone knows if it's now doable or if anyone has found clever work arounds that don't involve massive switch/if functions.

In short, I would like to be able to Lookup columns based on a dropdown or textbox value or variable. My source table column names are something as follows:

NAME | EVENT1 | EVENT2 | EVENT3 | EVENT4 | etc.

Let's say I want to LookUp whether a specific person has a value in any of the event columns. I'm currently using a switch that checks a dropdown for a value (Event 1), and if it's event 1, it will point to column 1, etc. This work around is wonderful when used in small tables, but I'm trying to build something that will eventually encompass 400+ events/columns. I do not want to have to update every inch of switch code in my app everytime I decide to add additional columns. I'm hoping to find a more elegant/clever solution.

Any ideas are appreciated. 

Cheers!

[Anonymous]

What's your data source? It seems like you could benefit from Pivoting your table.

[Primaryorange]

Hey thanks for the response!

I'm using tables in excel sheets right now. Can you explain a little on how pivot might help? Keeping in mind that if I rotate the table to have staff names as column headers, we will still be adding/removing names as frequently as we are events.

Thanks again!

[v-xida-msft]

Hi @Primaryorange ,

Do you want to list the column names from your data source within a Dropdown box, and select the column name as filter column dynamically?

Based on the needs that you mentioned, I afraid that there is no way to achieve your needs in PowerApps currenty. Regardless of whether or not you list your column names within a Dropdown box, a variable, a Text Box, the result (column name) from the Dropdown box, variable or Text box would be recognized as a Text string value rather than a valid column name from your data source.

Currently, as an available solution, I think the If function or Switch function may be the only solution to fix this issue currently. If you would like this feature to be added in PowerApps, please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

Best regards,

[Primaryorange]

Hi Kris, Thanks for responding. You are correct in what I'm trying to do.

I just wanted to update that I believe I've figured out a work around solution to this challenge that avoids scripting a 365 column switch. Incase anyone else needs to do something similar.

I've decided to use a series of concatenate and split to pull data from single cells of a single column, and add them to a collection/table within the app that I can play with. That way I can have PowerApps reference a static column in the source  (without having to constantly update this massive switch code everytime I want to add "new column" data). Columns become csv. Instead the app will now use the split to break up all the separated values into a collection that I can play with much more fluidly.

I'm early into implementing this work around so it's possible I might hit a wall. But for now its working for what I need it for.

Cheers and thanks to everyone who replied.

[poweractivate]

@Primaryorange 

1. You might benefit from the idea @Anonymous had about Pivoting it
2. However you do have an option without nesting
3. Instead of nested if's, try Coalesce(yourValue1, yourValue2, yourValue3) - it will evaluate in order then return the first non-blank value of the three in this case. Even with so many columns, it may be slightly easier to maintain. However, this may or may not work, because Coalesce may require same type. Moreover, you would need to now figure out which one evaluation stopped on, we don't believe Coalesce keeps track of the argument index where it decided to return the value from.
   1. (from  https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-isblank-isempty)
      1. "Coalesce( value1, value2 ) is the more concise equivalent of If( Not IsBlank( value1 ), value1, Not IsBlank( value2 ), value2 ) and doesn't require value1 and value2 to be evaluated twice.