You’re offline. This is a read only version of the page.
Hello all,
I have been searching for an hour or so now and cannot find a solution to what I am sure is a simple problem.
I have a collection that I initiliase on startup (from a linked Excel table as a data source). This is a simple small table. On the form I have two drop downs; one of these is populated with the Rows of the collection, i.e. Row 1, Row 2 and Row 3; the second drop down is populated with columns of the collection (but not column 1), i.e. Column 2, Column 3.
What I want to do is return the numerical value from the collection at the intersection of the chosen Row and Column - using the two drop downs.
I can successfully have a text label display a single lookup value from the collection by using the first drop down to select the row, however I need to 'hard code' the column lookup. I cannot make it work so that the two drop downs are effectively setting the row and column to lookup within the table.
As you can see from the snapshot below, the lookup works when I just type in the chosen column name (in this example "Invasive") - this returns the correct answer = 10
However, when I try and reference the column lookup using the other dropdown, it just returns the actual value of the drop down, it doesn't treat it as the column name:
Apologies for what I am sure is a really easy solution, I am new to PowerApps and just can't figure this out.
Appreciate any help you can provide.
Thank you very much, I can't believe I didn't try that! 😀
For anyone interested, here is the solution with simplified nomenclature:
Share
Report
All responses (
Answers (LookUp(colTable, Column1 = ddColumn1.SelectedText.Value, If(ddOtherColumns.SelectedText.Value="Column2",Column2,Column3))Collection Name = colTable, set up as follows:
Drop downs = ddColumn1 (populated with "Row1", "Row2", "Row3") and ddOtherColumns (populated with "Column2", "Column3").
This formula correctly returns the single value #1-6 at the intersection of the chosen row and column from the drop downs.
In this instance a simple If statement worked, for larger tables I might need to use Switch.
Thanks again.
Hi @grahamleigh ,
Your conclusion is correct! Basically in Power Apps functions column names are always need to be static. So, a choice to implement this is to use If or Switch statements, check the dropdown selected value and use corresponding static column names in the formula.
Hope this helps.
Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.
I Was hoping this was a simple syntax issue to resolve but apparently not! I have been searching for over a day now and can't find the answer to this - what I thought to be simple - question.
Is it not possible to dynamically select both row and column to lookup in a table collection? Surely it must be??
what's interesting, is that I have confirmed that both
ddActivity.Selected.Activityand
ddActivity.Selected.Valuereturn the correct value, but I am assuming they are returning it as a text string, and the argument in the LookUp formula for Result/ReductionFormula needs to be something else...a formula probably!
So I guess my question is actually how do I reference a collection by column name, by using a text string (which is variable) as the column name? Something like Column Name = "Text String"?
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup#syntax - this link indicates that "you can reference columns within the table" but doesn't say how (or how to do it if it's a collection).
Anyone any ideas? Thanks a lot.
#1
WarrenBelz
146,658
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,999
Most Valuable Professional
