Power BI and PowerApps

[GHock]

I am trying to create a PowerApp that will allow me to update records in a on-prem SQL table with in a Power BI report. 

I created the report with a table visual that displays the data from this SQL table. Then I added a PowerApp visual to the report and selected the fields to it. Then I selected to create a new PowerApp and then went on to create a basic App. I just have a gallery showing a column from the Power BI report. Then I add in a edit form and connected it to the same SQL table that the Power BI report pulls from. I select the edit form and then for the item property at the top I add in the following: Gallery1.Selected. Once I do that I get and error saying: Invalid formula, Expected a value compatible with DataSource. 

I have done something very similar to this on a different report and app and it worked fine. Everything looks to be setup the same way just looking at a different SQL table on the same server.

Any ideas why am getting that error?

[jhall]

That seems like it should work, but that's when it's time to verify our assumptions.  Have you tried to link Gallery1.Selected to a Data Table just to see all of the fields that it contains and compare that to your expected fields for the SQL table your Form is tied to?

[GHock]

I added a data table and then set the item property to Gallery1.Selected as you mentioned and it shows all the fields that I would expect to be there and they match whats in the SQL table. 

[jhall]

You've got the same DataSource for the Gallery and the Form, correct?  (I know...I know...dumb questions and all that)

[GHock]

Well that's where I am confused. For the Gallery I am using 'PowerBIIntegration'.Data for the data source and the edit form am using the SQL table. I have read that they should be the same but this is how I setup a different app that does the same thing but for a different report / SQL table and it works fine.

[jhall]

Well, assuming they're both pulling from the same place and haven't been altered (e.g. custom fields) within Power BI then I'd also assume this would work.  I think looking at the field list is the right choice though.  Something there isn't identical.  I wonder if this is one of those scenarios where the Microsoft "App" isn't formatting the data the same as the Microsoft repository.  Dates maybe?  

[GHock]

It has something to do with the date field. I am working with just pure test data right now so I took out the date field in the SQL table and recreated the Power BI report and the PowerApp with all the fields with out the Date field and it works as expected. Now that we know it has something to do with the date field. How do I check to see the differences between PowerApps and Power BI? 

[jhall]

Man, I wish the lottery worked on guesses of how software developers think.  I could retire.  

PowerBI is a UX for displaying data, so I'd bet it does some default formatting on dates that probably comes preconfigured.

Perhaps forcing the date into a specific format via a wrapper function?  I'd bet we can force a format on a date field as a data source, but will need to pull up a reference to even make the attempt.  

I'm ready to just tell everyone we're on Martian Standard Time and you have to self-adjust your own timezone-modifier dynamically by how many steps it would take you to get to the Latitudinal line for Elvis' grave.  That would make as much sense as most of the date-time functions out there.

[GHock]

When you say the Wrapper Function are you talking about word wrap? If not what does this wrapper function do? I did some looking but didn't see alot of info on it.

[v-xida-msft · Accepted solution]

Hi @GHock ,

Could you please share a bit more about your issue?

Do you add a PRIMARY KEY within your SQL Table?

Do you want to use the embedded canvas app (in your Power BI report) to edit/update record within your SQL Table?

If you want to use the embedded canvas app (in your Power BI report) to edit/update record within your SQL Table, please make sure you have defined a PRIMARY KEY in your SQK Table already.

In additon, in oder to fix your issue, you could also consider take a try with the following workaruond:

Set the Item property of the Edit form to following:

LookUp('[dbo].[YourSQLTable]', PrimaryKeyColumn = Gallery1.Selected.PrimaryKeyColumn)

Please take a try with above solution, then check if the issue is solved.

The difference between Power BI and PowerApps as below:

Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Actually, Power BI is a visualization degin tool, which could be visualize your business data. You could use Power BI to display your data in visualization, but you could not write data back to data source through Power BI.

PowerApps is a suite of apps, services, connectors and data platform that provides a rapid application development environment to build custom apps for your business needs. You could use PowerApps app to connect to your underlying data sources, and do some CURD opearion to your data source. PowerApps app could write data back to your underlying data source.

---

Best regards,