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.

Power BI Snack: Introducing Data Profiling

Case
When data is not extracted from a cleaned up or validated environment, such as a Data Warehouse or Data Mart, the first step is to analyse and possible clean up the data. How can Power BI help in this?

Data Profiling in Power Query Editor

Solution
The release of October 2018 Desktop update introduced a new (preview) feature that can profiling the data in the Power Query Editor. With this, you can do some quick analysis over your data and you can easily identify errors and empty values in a column.

For this blog post we use sample data from WideWorldImporters. We have saved the data in a CSV file, because it is more common to do this kind of analyses when you load raw data from Text or CSV files into your Power BI Model instead of using validated data from a star schema in a Data Mart.

Apply data profiling in Power BI
Because it is a preview feature, we have to turn this feature on. Go to "File - Options and settings - Options - Preview features" and select Enable column profiling.

Power BI Desktop - Enable Preview feature

Open the Power Query Editor and load your data. In our case it is a CSV file that contains raw data about sales orders. Go to the "View" tab and select Column quality. With this option you can see whether your data contains errors or empty values, also known as "null" values.

Power BI Desktop - Column quality in Power Query Editor

Besides this, you can also view the total of unique and distinct values. Go to the "View" tab and select Column distribution. Unique values indicates how many values occur only once and distinct values means how many different values there are in the specific column. Now the Query Editor will also recommend to do a quick fix if you want, for example remove the duplicate values in a column.

Power BI Desktop - Column distribution in Power Query Editor

Note:
Be aware of the fact that column profiling is only based on top 1000 rows (preview results of the query).

Conclusion
In this post you saw a new feature called "Data Profiling". It is a good start and useful to use this for doing some quick analyses over your data. Note that this feature is in preview.

The program team of Power BI has announced to add more capabilities for data profiling, so we are very curious about what this will bring even more in the future.

SSRS Snack: Download all SSRS reports

Case
I want to download all my SSRS reports (and data sources) from my Reporting server, but not manually. Is there a solution?

My Reporting Server

Solution
The easiest way to do this is with some PowerShell scripting and the SSRS webservice. The following PowerShell script recreates the SSRS folder structure on a local drive and downloads all reports (*.rdl) and all data sources (*.rds) to the corresponding folders.

Note 1: you need full access if you want to download everything
Note 2: empty folders are not recreated locally
Note 3: It only works for SSRS 2012 and higher (webservice differs for older versions)


1) PowerShell ISE
Open PowerShell ISE and copy and paste the code below. Determine the local folder (line 7) and provide the URL of the Reporting server on line 8 (without "/reports"). For this example the SSRS server is located on a Virtual Machine in Azure.

###################################################################################
# Download Reports and DataSources from a SSRS server and create the same folder
# structure in the local download folder.
###################################################################################
# Parameters
###################################################################################
$downloadFolder = "c:\temp\ssrs\"
$ssrsServer = "http://myssrs.westeurope.cloudapp.azure.com"
###################################################################################
# If you can't use integrated security
#$secpasswd = ConvertTo-SecureString "MyPassword!" -AsPlainText -Force
#$mycreds = New-Object System.Management.Automation.PSCredential ("MyUser", $secpasswd)
#$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -Credential $mycreds

# SSRS Webserver call
$ssrsProxy = New-WebServiceProxy -Uri "$($ssrsServer)/ReportServer/ReportService2010.asmx?WSDL" -UseDefaultCredential

# List everything on the Report Server, recursively, but filter to keep Reports and DataSources
$ssrsItems = $ssrsProxy.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "DataSource" -or $_.TypeName -eq "Report"}

# Loop through reports and data sources
Foreach($ssrsItem in $ssrsItems)
{
    # Determine extension for Reports and DataSources
    if ($ssrsItem.TypeName -eq "Report")
    {
        $extension = ".rdl"
    }
    else
    {
        $extension = ".rds"
    }
    
    # Write path to screen for debug purposes
    Write-Host "Downloading $($ssrsItem.Path)$($extension)";

    # Create download folder if it doesn't exist (concatenate: "c:\temp\ssrs\" and "/SSRSFolder/")
    $downloadFolderSub = $downloadFolder.Trim('\') + $ssrsItem.Path.Replace($ssrsItem.Name,"").Replace("/","\").Trim() 
    New-Item -ItemType Directory -Path $downloadFolderSub -Force > $null

    # Get SSRS file bytes in a variable
    $ssrsFile = New-Object System.Xml.XmlDocument
    [byte[]] $ssrsDefinition = $null
    $ssrsDefinition = $ssrsProxy.GetItemDefinition($ssrsItem.Path)

    # Download the actual bytes
    [System.IO.MemoryStream] $memoryStream = New-Object System.IO.MemoryStream(@(,$ssrsDefinition))
    $ssrsFile.Load($memoryStream)
    $fullDataSourceFileName = $downloadFolderSub + "\" + $ssrsItem.Name +  $extension;
    $ssrsFile.Save($fullDataSourceFileName);
}

There is also code (comment out) to provide a username and password in case you cannot use integrated security. Replace line 16 with the code on lines 11 to 13 and provide the username and password.

2) Result
Running the script in PowerShell ISE downloads all reports and data sources.

Downloading all reports

Summary

This is a 'quick & dirty' script to download all SSRS reports at once. You can adjust the script to you own needs and make it more beautiful. A upload(/deploy) script will be provided in a next blog post.

Power Apps Snack: Pass values to other screen

Case
I want to navigate to a new screen and get a value from the calling screen to filter a specific record on that new screen. Most examples (including 'Start from data') filter a record with the selected item from a specific gallery (on a specific screen) with:

BrowseGallery1.Selected

However I want to call this screen from various calling screens (not just one). Is there a push meganism instead of  a pull meganism to filter the record on the new screen?

Navigating to a new screen from various screens

Solution
The solution is to pass a value from the calling screen to the new screen. The Navigate function has an optional argument called 'UpdateContextRecord' which allows you to pass a record to the new screen which on its turn updates the context variable on the new screen. Then you can use that variable to filter a specific record.


1) Calling screen(s)
In PowerApps a record with one column (a variable) looks like:

{myIntColumn:123}

If you put this in the 'UpdateContextRecord' argument from the Navigate function it looks like:

Navigate(myNewScreen, 
         ScreenTransition.None, 
         {myIntValue:123})

The hardcoded '123' should of course be replaced with some code to get the value from a gallery column on the calling screen. The Value function converts the string to a number:

Navigate(myNewScreen,
         ScreenTransition.None,
         {myIntValue:Value(ThisItem.myIntColumn)})

You need to repeat this on all calling screens that want to navigate to this new screen.

2) New screen
On the new screen this record will be pushed to a new context variable which can be used in for example a LookUp function in the Item property of a form:

LookUp('[dbo].[myTable]', myIntColumn=myIntValue)

Changed from pull to push

Alternative form item formula:

First(Filter('[dbo].[myTable]',myIntColumn= myIntValue))

Conclusion
In this post you learned how to pass through variables from one screen to an other. This can be used to pass through values for filters from several screens instead of just reading values from one screen.