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

How to perform sort of Excel Index(Match()) on Powerapps

Hello everyone,

I'm trying to find a solution for my app, but so far I got nothing. I'm going to explain the problem:

 

I have an excel file (connected as datasource) with daily prices for a cerain product. These prices depends on the period (year-month) and location, as you can see bellow:

juanrodrigues13_0-1618863874535.png

For example: for the period "2021-Apr" and location "AT/SFO", the price is "100", as shown on the image.

 

What I need in the PowerApps is: based on user's input (Period and Location), the PowerApps needs to return the correct price.

juanrodrigues13_0-1618864823963.png

 

I've tried to use the function LookUp(), with dynamic column name, but as far as I know, PowerApps doesn't support it.

 

Could someone help me please?

1 ACCEPTED SOLUTION

Accepted Solutions

@juanrodrigues13 ,

I was responding to your question as to whether the data source would make a difference. As I mentioned, you would need to "hard code" the column names - something like (I have assumed control names and valid values here)

With
   {wLoc:ddLocation.Selected.Location},
   Lookup(
      YourListName,
      Period = ddPeriod.Selected.Period &&
      Switch(
         wLoc,
         "AT/SFO",
         'AT/SFO'=wLoc,
         "HU/SFO",
         'HU/SFO'=wLoc,
         "AT/RSO",
         'AT/RSO'=wLoc,
         "HU/RSO",
         'HU/RSO'=wLoc
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @juanrodrigues13 ,

Power Apps does not support dynamic column names, so you cannot do Excel Index-type queries. You would have to hard-code the location possibilities and would need a drop-down to limit the input to valid values.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Hi @WarrenBelz, thx for the answer!

So, there is no way to perform this type of query? Even if I try to change my datasource (excel to sharepoint, SQL, etc)?

@juanrodrigues13 ,

No - you cannot refer to field names dynamically in Power Apps.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

@WarrenBelz, ok, I understood we cannot use dynamic names, but I mean: is there another solution to solve my problem? 

@juanrodrigues13 ,

I was responding to your question as to whether the data source would make a difference. As I mentioned, you would need to "hard code" the column names - something like (I have assumed control names and valid values here)

With
   {wLoc:ddLocation.Selected.Location},
   Lookup(
      YourListName,
      Period = ddPeriod.Selected.Period &&
      Switch(
         wLoc,
         "AT/SFO",
         'AT/SFO'=wLoc,
         "HU/SFO",
         'HU/SFO'=wLoc,
         "AT/RSO",
         'AT/RSO'=wLoc,
         "HU/RSO",
         'HU/RSO'=wLoc
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (2,164)