Power Apps for Power BI - problem with formula

[Jan_Trummel]

Hello to the forum,

I would like to write back to my data using Power Apps and update my Power BI report. I am following the video from BAS:

YouTube: Can’t INPUT DATA in Power BI? Here is a WRITE BACK Option with Power Apps! 

My data is in the form of an Excel spreadsheet. The Excel file itself is in OneDrive for Business.

My table has the name "Sales". The Excel file ist stored in OneDrive for Business.

In Power BI Desktop, I imported the Excel file via the web connector.

I used the Web connector to load the data

I then inserted the Power Apps visual into the report and dragged the Amount field into it.

I put the field "Amount" in the Power Apps visual.

In the visualization I then clicked on the Create new button.

I hit the "Create new" button to open the Power Apps Studio

The app then redirected me to the Power Apps Studio. As you can see, the Power BI integration is included in the tree view.

My Power App has the Power BI integration

I then added the Excel file as a data source via the OneDrive for Business connector.

I choosed my Sales table as data source for the app

I then inserted a form and selected my Sales table as the data source. I selected Amount and Customer as fields for the form. I gave the form the name frmSales.

I added a form and choosed the Sales table as data source

Then I wanted to set up the Power Apps visual to interact with my Power BI report. To do this, like Bas does in his video, I used the LookUp function for the Item property of my form:

LookUp(Sales, Customer = First(PowerBIIntegration.Data).Customer)

However, the formula shows me an error:

There is an error in my formula

The error message is:

The expression was intended to look up a record in the `Sales` table where the `Customer` field matches the `Customer` value from the first row of the `PowerBIIntegration.Data` table.

However, there are two main errors in the expression:

1. **Name isn't valid**: The error indicates that the `Customer` variable is not recognized in the context of the expression. This suggests that the reference to `Customer` is not correctly scoped or defined.

2. **Incompatible types for comparison**: This error indicates that the comparison between the `Customer` variable and the `Customer` field from the `PowerBIIntegration.Data` table is not valid due to a type mismatch. Specifically, it suggests that one of the values being compared is of type `Text`, while the other is of type `Error`, which cannot be compared directly.

To resolve these issues, the expression needs to ensure that the `Customer` variable is properly defined and that both sides of the comparison are of compatible types.

Can anyone help me? Why is it not working? I think I did everything like Bas did in his video.

Thank you and best regards

[Jan_Trummel]

Hello @lbendlin  and @ThxAlot ,

thanks for your answers. You're right. The Customer field isn't included in the visualization at all.

I've now dragged it into the Power Apps visualization in the Power BI Desktop file.

I added "Customer" in the Power Apps visual.

The field is now definitely included in the PowerBIIntegration object. I checked this by dragging a data table onto a new screen and adding both fields.

The "Customer" field is now included in the Power Apps Studio

In a text field, I checked whether the Customer field could be addressed. I assigned this formula to the Text property:

First(Sales).Customer

With a text field I proved, that "Customer" is now available

The LookUp function of my form still shows an error message.

LookUp(Sales, Customer = First(PowerBIIntegration.Data).Customer)

But this time with different text. So it seems that adding the Customer field to the visualization in Power BI has made a difference.

Here is the error message:

The expression was intended to look up a record in the `Sales` table where the `Customer` field matches the `Customer` value from the first row of the `PowerBIIntegration.Data` table.

However, there are two main errors in the expression:

1. **Critical Error**: The name 'Customer' is not recognized, which suggests that the expression is trying to reference a variable or field that does not exist or is not defined in the current context.

2. **Severe Error**: There is an incompatible types issue, indicating that the comparison being made between the `Customer` field (which is expected to be of type Text) and the result of the `First` function (which is returning an Error type) cannot be performed. This likely means that the `First` function is encountering an error when trying to access the `Customer` field from the first row of `PowerBIIntegration.Data`.

Overall, the expression fails due to an unrecognized variable and a type mismatch in the comparison.

Power Apps still claims that the field does not exist. I cannot understand this, since I have proven that the field exists using the text field.

I then tried the following: I replaced Customer with Amount in the LookUp formula. In my opinion, this should definitely work, since this field was included in the Power Apps visualization from the start.

LookUp(Sales, Amount =First(PowerBIIntegration.Data).Amount)

But here again I get an error message:

The expression was intended to look up a record in the `Sales` table where the `Amount` matches the `Amount` from the first row of the data provided by `PowerBIIntegration`. Specifically, it aims to find a sales record that corresponds to a specific amount.

However, there are two main errors in the expression:

1. **Name isn't valid**: The error indicates that the variable `Amount` is not recognized in the context of the expression. This suggests that `Amount` may not be properly defined or scoped within the current context.

2. **Incompatible types for comparison**: This error arises because the expression attempts to compare a `Text` type (the `Amount` variable) with an `Error` type (likely resulting from the `First` function not returning a valid row). This indicates that the `First` function may not be returning a valid record, leading to a type mismatch during the comparison.

Overall, the expression needs to ensure that `Amount` is correctly defined and that the data being accessed is valid to avoid these errors.

Do you have any idea what I'm doing wrong?

[lbendlin]

What's the field type of the Customer column in the Sales collection?

[Jan_Trummel]

Hi @lbendlin ,

the type of Customer is Text.

[ThxAlot]

Obviously, you didn't pass column 'Customer' from PBI to PApps in this step.

[lbendlin]

There is no Customer field in the Values well of the PowerApps visual.