You’re offline. This is a read only version of the page.
Hi! I'm new in Powerapps and trying to write the formula as much as I can.
I would like to do JOIN table as in SQL query, however Powerapps don't work in this way.
There are two tables.
Result I want : Show the Description from Table2 which Table1.Detail1=Table2.Code.
In my Powerapps, the Description will show at a Label "D1Description" in the Gallery(which data source is Table1).
I do Filter in Label "D1Description" based on another Lable "D1Code"(which is Table1.Detail1)
AND Table2.Detail is "D1".
Error Filter - Text value
But I get this ERROR MESSAGE : "Expected Text Value" always, even I searched and tried other function.
SQL Database examples:
Table1
Column Name : Item Detail1 Detail2
Value Row1 : 001 A Aa1
Value Row2 : 001 B Bb1
Table2
Column Name : Detail Code Description
Value Row1 : D1 A Group 1
Value Row2 : D1 B Group 2
Value Row3 : D2 Aa1 Group 1 Alpha
Value Row4 : D2 Bb1 Group 2 Beta
SQL Query
SELECT Description
FROM Table1 X
LEFT JOIN Table2 Y on Y.Code=X.Detail1 and Y.Detail = 'D1'
Appreciate for your advise and guidances.
Thank you!
Dear Phoebe,
Thank you very much for your reply!
Sorry that I still don't really get it even though I read the docs.microsoft pages.
1) AddColumn
Based on my example above, create a Gallery(Data Source Table 2) to show multiple columns(including a column from Table 1), does AddColumn(Filter(..)) mean it will create new columns for the Results?
2) Delegation
Everytime I write formula Filter(...) or/and contains "=" then Delegation Warning will happen.
However, the database contain of data more than 2000 rows in SQL Server.
I really confuse on the references in docs.microsoft webpages and confuse on how to modify the formula to delegable.
Is there any precise explaination on "modify the formula to delegable function"?
Pardon me if I asked silly questions.
Thanks alot!
Hi @Anonymous ,
1)AddColumns function is used to adds a column to a table.
It can bu used to reshape the table structure.
I think use this can achieve the function of "join".
Here's a doc about this function for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-table-shaping
2)If your record is from 500~2000, this will not effect your formula.
Just do not forget to change the non-delegation limit to 2000.
If your record is larger than 2000, then you need to modify your formula to delegable function.
Here's a doc about delegation for your reference:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/delegation-overview
Thank you so much Jeff!
I have better picture on Filter and LookUp now!
I searched Join table alternative in internet, I saw some said AddColumns(Filter(...)), may I know is this work the same?
Or as you mentioned, it is same like Filter(without AddColumns function) returns a table with Columns and filtered records?
Besides this, if the record is more than 500, is the formula LookUp(in your answer above) suggested?
Thank you so much!
In your screenshot the Filter() is in a Text property. Filter() returns a table of records and that is why you are getting the error message. You can use a Lookup() to retrieve a signle record and display the Description information.
Example:
Lookup('Table2', Code=D1Code.Text And Detail = "D1").Description
#1
WarrenBelz
637
Most Valuable Professional
#2
stampcoin
570
Super User 2025 Season 2
#3
Power Apps 1919
473
Share
Report
All responses (
Answers (