Power Apps Snack: RemoveIf not deleting all records

Case
I my PowerApp I want to delete records in a table with RemoveIf, but it doesn't remove all records selected with the condition parameter.

Remove record(s) in PowerApps

Solution
This problem occurs when the source table has more than 500 records. Because RemoveIf is a non-deligated function it will not deligate the delete instruction to the source. Instead it will first get 500 records (that number can be changed) and then delete only within these 500 records.

There are two solutions:

1) Change Data row limit for non-delegable queries
One option is to change that magic 500 number to for example 1000. You can do this in the App Settings under Advanced settings, but there is one caveat when changing that. It could cause performance issues because it will then first 'download' the 1000 records before it will start deleting.
And what if you have tens of thousands of records or even millions? That is not something you want to 'download' to your app! So be careful with this magic number.

Data row limit for non-delegable queries

2) Use Microsoft Flow
An alternative is to use Microsoft Flow to delete records in large tables. You can create a new Action for your button that executes a Microsoft Flow that does the deletion via a Stored Procedure or query.

Adding Microsoft Flow to your PowerApps

There is of course a little downside. The free edition of Flow only allows 750 runs per month and the Office 365 only 2000 runs per month. Above these numbers you need Flow Plan 1 or 2 with 4500 and 15000 runs per month. Or you can buy an additional 50000 runs per month for USD $40.00. If you are sure you don't need these high numbers then you have a good, free alternative.


Conclusion
This post showed you two alternative solutions both with their own pros and cons. There is a third alternative which I haven't tested yet and that is to create a custom connector. Or you could just add a PowerApps Idea to suggest Microsoft to solve this and post the URL in the comments below.

Azure - On-premises data gateways

Case
If you want to use on-premises data in Azure or Power BI you need to install a so called gateway. A quick search resulted in four possible gateway downloads! Which one do you need? It's so confusing!

Microsoft Gateways for Azure and Power BI

Solution
There are two different gateways but with several names, versions and purposes:

• Data Management Gateway
  This gateway is used for Azure Data Factory and Azure Machine Learning, but it was also used for Power BI. However, Power BI now uses the On-premises data gateway! I think / hope that this gateway will be deprecated and replaced by the On-premises data gateway in the near future.
  Download (32 and 64bit): https://www.microsoft.com/en-us/download/details.aspx?id=39717
• On-premises data gateway
  This gateway comes in three flavors, but you can only install one per server.
• On-premises data gateway
  This installer is for Power Apps, Logic Apps, Microsoft Flow and Power BI (Enterprise only).
  Download (64bit): https://www.microsoft.com/en-us/download/details.aspx?id=53127
• On-premises data gateway Power BI
  This installer is also for Power Apps, Logic Apps, Microsoft Flow and Power BI, but has an extra option for a Personal Power BI gateway. If you do not need that, you could just use the installer above, which will have the same result.
  Download (64bit): https://powerbi.microsoft.com/en-us/gateway/
• On-premises data gateway Azure Analysis Services
  This is a (temporary) installer for Azure Analysis Services (AAS) only and it can used be for one AAS server only. It will soon merge with the standard installer.
  Download (64bit): https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway

Use source for multiple online services

Since you can only install one gateway per server, the On-premises data gateway has some challenges if you want to use your on-premises source for multiple online services like Power BI and AAS or for two AAS servers. In that case you install the first gateway on the source server itself and a second gateway on a separate on-premises server that can reach the first source server. A bit expensive to a have a server running for a data gateway only, but for now this is the only solution.

One source and two online services: two servers with their own gateway

Note: details per gateway will be posted in separate posts (AAS)

Extracting tweets with Microsoft Flow

Case
I want to extract tweets from twitter to my data warehouse without writing code. Is that possible?

Extract #SSIS Tweets

Solution
There are of course several options with traditional ETL tools like SSIS with custom .NET code, a custom twitter tasks or even by calling the Twitter API within R. But Microsoft also recently introduced Microsoft Flow and Microsoft Azure Logic Apps which can connect to various apps and services (like Facebook, Google Drive, GitHub, Twitter, SharePoint, etc.) to collect data, synchronize files or get notifications of certain events, without writing any code! The basics of Flow are free and Logic App is the professional version in Azure with of course some more options.


Solutions:
A) Microsoft Flow
B) Microsoft Azure Logic Apps


1) Microsoft Flow
First go to flow.microsoft.com and login with your Microsoft Account. You can now search for existing templates or create a flow from blank. We will start with a blank flow for this example.

Searching for Twitter templates or use the blank flow

2) Twitter source
In the blank flow you first need to search for a trigger. In this case we want to search for tweets on twitter. The trigger is 'When a new tweet is posted'.

Blank flow searching for Twitter trigger

When you select the Twitter trigger you need to setup the Twitter connection and the search text. For this example I will login with my Twitter account and search for tweets with #SSIS.

Connecting to Twitter and setting up search

3) Filtering annoying job tweets
Of course we want to filter those annoying job tweets. Therefore we need to add a condition. In the condition pane you can select fields from the Twitter 'source' to filter on. In this example we are filtering tweets that contain the word 'job' (probably not the perfect filter).

Add ' job' filter

4) Add SQL Destination
Now we need to store the tweets in a (Azure) SQL Server database. For this example we created a simple table with all nvarchar(255) fields:

CREATE TABLE [dbo].[Tweets](
    [Name] [nvarchar](255) NULL,
    [TweetText] [nvarchar](255) NULL,
    [TweetTime] [nvarchar](255) NULL
)

Add a new step and search for SQL Server - Insert row. Then enter the credentials from your database, select the table (must be an existing table) and map the Twitter fields to the appropriate table columns.

SQL Destination - Insert row

5) Create flow
After the last step has been added click on Create flow and then on Done. Now your newly created flow is ready to run. Watch the table!

Create flow

The result

Conclusion
Microsoft Flow is very easy to use and you don't need programming skills. There are dozens of apps and services to connect to.
But to keep it for free you can only check once each 15 minutes and only do 750 runs a month. That should be enough for at least on flow, but if that's not enough you can either switch to a premium account which gives you more runs, more checks and even more services to connect to. Or you could switch to professional Microsoft Azure Logic Apps.