Using Power Apps to append data into Power BI

[darentengmfs]

Hello,

I am trying to create a report that gives me a chart of open ticket counts for each day.

For example, on 7/1/2020, there are 200 tickets open. The data on 7/1/2020 will stay that way even if there are tickets closed on 7/2/2020. This will be a line chart seeing how many active tickets were present on each particular day. I have thought of using a count with Date tables on rows that are between Ticket Open date and Ticket Close date, but I do not have Ticket Close date column, and any tickets that are closed are erased from the data.

What I thought of is using Power Apps to manually append the data instead. Every day, I will go into Page 1 of the report, it will give me a count of the ticket that is still active, then, I will go to Page 2 and enter the number into the Power App visual, and that number will be appended to a new table, Table X. My chart will then be referencing to Table X. In short, someone will have to enter the number every day.

However, I do not have experience with Power Apps. Does anyone know how to implement what I need?

Thanks!

Daren

[mwegener]

Hi @darentengmfs ,

I would consider the following idea.
Create a data flow in PowerApps, which saves the ticket status in the CDS in the morning. The data record should not be overwritten via an ID / key (date). You should then be able to load the CDS Entity into Power BI.

This inspired the idea.

https://www.thebiccountant.com/2020/04/13/create-a-load-history-or-stage-in-cds-instead-of-increment...

[edhans]

@mwegener - doesn't CDS require a premium connector for Power Apps licensing?

I know CDS is a much preferred data source than SharePoint Lists, but the latter is included iwth Office 365 Power Apps licensing in most cases. CDS becomes $40/mo I believe.

[mwegener]

Hi @edhans ,

Licensing is not my specialty and is no fun, but here is my current level of knowledge.

There are different types of license.

-  Power Apps & Power Automate use rights included with Office 365 licenses

-  Power Apps & Power Automate use rights included with Dynamics 365 licenses

-  Power Apps per App Plan

-  Power Apps per User Plan

-  Power Automate per User Plan

-  Power Automate per Flow Plan

Except for the Office Plan, all should include Common Data Services. But please check that again.

https://download.microsoft.com/download/9/5/6/9568EFD0-403D-4AE4-95F0-7FACA2CCB2E4/Power%20Apps%20Po...

@darentengmfs  Which license do you use?

[edhans]

I believe that is correct @mwegener - which was why I recommended SharePoint LIsts. I fully agree CDS is a better long term solution, especially for projects that will have thousands of records, but it does come with the added expense of $7 to $40 per month per user depending on what other plan is selected.

[darentengmfs]

Hi @edhans and @DataZoe 

Thanks for the thorough explanation.

I am opting to use Sharepoint Forms to update my data.

However, I read it here that I will have problems with the data gateway if I connect to both SQL and Sharepoint.

https://community.powerbi.com/t5/Power-Query/How-to-configure-On-premises-Gateway-to-SharePoint-Fold...

I don't know if it has been resolved, so could you share some input to me if you have had experience with connecting Power BI to both SQL server and Sharepoint in the same report?

Thanks!

Daren

[edhans]

Hi @darentengmfs That is from 2016. MS upgraded the service at least in 2017 to use both on-prem and cloud data in the same report. I use SharePoint with on-prem SQL Server every day in Power BI.

[edhans · Accepted solution]

@darentengmfs - this really isn't a Power BI question. Power BI will read data from many sources, as you know.

You are taking it to the next level with using Power Apps to record your data, vs someone doing it in Excel or with another app.

As @DataZoe there are ways to add data without Power Apps, like using the default Forms in SharePoint lists.

I love using Power Apps though to do what you want to do, but if you have no experience with it, then I cannot recommend the App in a Day course highly enough! It is free, and here. 

Once you get that done, you just create your Power BI report and set the refresh schedule. You can also trigger a refresh from the Power Apps app via Power Automate, but you are limited to 8 refreshes a day with a Power BI Pro license. Power Automate refreshes and Scheduled Refreshes add up. Once you've done 8 refreshes, no more will happen until the next day. If you have Premium capacity, it is 48 per day.

If you get bold, then the Direct Query route @DataZoe mentioned can be refreshed unlimited times, but this has drawbacks:

• Direct Query requires a real server - SQL Server, Azure SQL, etc.
• You have to have an Enterprise Gateway. Personal will not work with Power Apps/Automate.
• Connecting to such a server requires a Power Apps license that is either $7 per mo for one app, or $40/mo for unlimited apps. SQL Server is a premium connector for Power Apps.
• Direct Query reports in Power BI are much more limited than Import apps. Your transformations are limited, as are some DAX functions and visuals.

Once you get your head around what you want to do and have done the App in A Day course, the Power Apps community can jump in and assist with building the app.

My personal advise - go slow. Use SharePoint Lists as your data source for the app. Don't jump in too deep and get frustrated with gateway configs, proper indexes and whatnot in a SQL Table. Power Apps requirements for SQL Server are higher than Power BI. Both can read any SQL table. But to write, Power Apps needs a unique index field that is hopefully generated by the server on the fly, or you get into creating GUIDs or your own incrementing logic.

[DataZoe]

@darentengmfs There are some licensing considerations for embedding Power Apps into Power BI. But, it is doable, and there is a short video here https://youtu.be/us0hPFwQ4Zs (Guy in the Cube ~10 mins) that shows one way. 

Another options would be:

1. to have it as a sharepoint list as the data source of the table in Power BI, so you could enter the new row for each day there, then Power BI picks it up on a scheduled refresh or manual refresh after it's been added.

2. create a table in Azure SQL Database and connect it to Power BI Desktop as direct query. You can update the table directly, or set up a Power Automate (Previously known as Flow) to update it from another source on edit (excel file, sharepoint list, whatever).