0845 643 64 63

Jon Fletcher

Download Power BI Reports using PowerShell

In this blog post I will be sharing a PowerShell script that allows multiple Power BI reports to be downloaded at once.

In the Power BI service, there is no way of downloading multiple Power BI reports at once. Therefore, users must download files one by one which is slow, time consuming and inefficient. Thankfully, there are ways around this, one of which is using PowerShell.

The script is downloadable at the bottom of the page as a txt file. To use the script there are three steps to take.

The first step is to install and import the MicrosoftPowerBIMgmt PowerShell module. The following code should do the trick.

The second step is to fill in the variables at the top of the script:

  • Workspace Name
  • Report Name
  • Download Path

Workspace Name – The name of the workspace in which the report or reports are stored.

Report Name – This can be configured to download one, multiple or all reports in the workspace.
One report = @(‘Report 1’)
Multiple reports = @(‘Report 1’, ‘Report 2’, ‘Report 3’)
All reports = @()

Download Path – The folder where the report or reports will be saved to.

Once these variables are assigned the script is ready to be executed.

The third and final step is to sign into Power BI, a prompt asking you to sign into Microsoft Azure will pop up. Use the credentials you would use to sign into PowerBI.com.

The script should now run in full and bring the PBIX files into the designated folder.

The script is attached as a txt file.


This script will only save your Power BI reports if they can be downloaded as PBIX files. If Large dataset storage format or Incremental refresh has been enabled the reports can’t be downloaded as a PBIX file. To back these reports up I would recommend Power BI Sentinel – https://www.powerbisentinel.com/

Creating a quadratic solver in Python

In this blog post, I will be showing you how to create a python function that will solve any quadratic equation.

Firstly, what is a quadratic equation?

A quadratic is an algebraic equation that can be arranged as ax2 + bx + c = 0 where a, b, c are known and a ≠ 0. If a = 0 then the equation becomes linear as there isn’t a x2 term.

Secondly, how to solve this equation? There are different ways to solves quadratic equations including:

  • By inspection
  • Completing the square
  • Quadratic formula

We will be using the quadratic formula which is:

Where a, b & c are the known coefficients when the equation is arranged into ax2 + bx + c = 0

We use the Quadratic formula because it can be used in any situation and there is no nuance to it like the other methods.

The first part of the code is to define a function that will accept 3 parameters: a, b & c

To use the quadratic formula, we will use the square root function from the imported math module.

Before plugging the parameters into the formula, we can work out how many real roots we will get by using the discriminant. The discriminant is b2 – 4ac.

If b2 – 4ac > 0 then there are 2 real roots.

If b2 – 4ac = 0 then there is 1 real root.

If b2 – 4ac < 0 then there are no real roots. (There are complex roots using imaginary numbers, but we won’t go into that here, maybe in a part 2)

Let’s add calculating the discriminant and an IF statement depending on the result to the function.

Next, lets plug a, b & c into the quadratic formula.

When the discriminant = 0 we only need to find the value of root1 as it’s equal to root2. Knowing the discriminant = 0, we could simplify the equation to

In the code I have chosen to leave the full formula in.

That’s our code complete, finally let’s test the function with the 3 different discriminant scenarios.

The function works as intended, the code is attached as a txt file.

How to create a toggle button in Power BI

In this blog post I will be showing you how to use a toggle button in Power BI. A toggle button in Power BI allows users to quickly switch between two options.

Our first step is to create a toggle button as Power BI doesn’t have one by default. You can import one but by creating it yourself, it gives you more control.

Our toggle button is created by having two images of identical size over the top of each other and using bookmarks to flip between the two. Therefore, you can create your images using any software you like. I’ll be using PowerPoint and importing shapes.

In PowerPoint, import the Flowchart: Terminator shape.

Then, import an oval and shape it into a circle. Change the shape fill to white or grey and move the circle so that it fits inside the curve of the curved rectangle.

Once that is done, copy and paste the shapes that form the toggle button and move the white circle to the other side of the curved rectangle. Next, import a textbox over the shapes and include the text you would like for the toggles.

My Power BI report is going to toggle world record times between Men and Women, so my toggles look like this:

It is crucial that these images are the same size, therefore we need to make sure there is no whitespace around the toggle images. To ensure this. I copy the two images into everyone’s favourite image editor – Microsoft Paint! There, crop all the whitespace around the shapes before saving the images as .png files.

It is now time to import these two created images into Power BI and align them such that they’re directly on top of each other. This is required because the image’s visibility will be toggled to give the impression it’s one button instead of two images. You can ensure the images are exactly aligned using the position and size properties.

Toggling the visibility of the images and associated charts is done using bookmarks. The below screenshot shows for the Women’s bookmark: all men related items are hidden and all women related items are shown.

The final step is to action the images such that when clicked on, the bookmark is activated and toggles to the other image and charts. When the women’s toggle is clicked activate the men’s bookmark.

Hopefully you should end up with something like this:

Clicking on the toggle in the top right corner toggles us to the women’s chart.

Clicking on the toggle in the top right corner again takes us back to the men’s chart.

This is my 2nd blog post that uses PowerPoint to enhance my Power BI experience, please see the first blog post here: https://www.purplefrogsystems.com/blog/2019/12/creating-power-bi-layouts-using-powerpoint/

How to create the Fibonacci Sequence in Python

This blog post will go through how to create the famous Fibonacci sequence in Python.

Each number in the Fibonacci sequence is the sum of the previous two terms.

The sequence starts: 0, 1, 1, 2, 3, 5, 8, 13, … and carries on infinitely.

The Python code I used is:

The first two terms are entered into our list, then we specify the number of terms wanted in our sequence.

Next, we loop through from 2 to the number of terms we wanted appending the sum of the previous two terms.

Printing our list gives the first fifty terms of the Fibonacci sequence.

Join 2 Python lists together using nested loops

In this blog post I will show you how to join two 2D Python lists together.

The code is in the screenshot below.

Lines 1 – 2 are two lists that are going to be joined, line 3 is an empty list where the output will be appended to.

Lines 4 – 5 are two loops (one nested inside the other) which cycle through the records in both lists, line 6 checks whether the first items (index 0) in the two records from each list that are currently i & j match. If yes: the Key, Capital and Country are appended to our new list.

Lines 9 – 10 show the output record by record showing the join has worked successfully.

This code can be expanded for 3 lists, the code would have a 3rd for loop and an extra check in the if statement to find the correct record in the 3rd list to join.

Joining lists together in Python is useful when there is data in different lists and it would be beneficial if it were combined.

Power BI – Enable Load

In Power BI Power Query there is an option to enable or disable whether a table is loaded into the report. The option ‘Enable load’ can be found by right clicking on the table. Typically, by default, the load is already enabled.

There is also an option ‘Include in report refresh’ which lets a user stop a table from refreshing when they refresh the entire report. This maybe useful for static tables or tables that are large which take a long time to refresh and a user wants to concentrate on how other tables are refreshing.

Once a user disables the ‘Enable load’ option, the table name turns italic which is an easy way for users to determine whether a table will be loaded or not.

After applying these changes, no data has been loaded into the report.

To re-enable the load, jump back into Power Query, right click on the table and ‘Enable load’.

Finally, some scenarios where it might be useful to disable loading a table:
– Disable loading tables in Power Query that were only ever stepping stones to create other tables
– See how removing a table effects your report before deleting it
– Removing a table that might be required again in the future

Tabular Cube Processing Report

I have created a Power BI report which provides detail on the state of processing in a Tabular Cube.
The report uses the cube’s dynamic management views to provide information about the cube’s partitions.

To use the tabular cube processing report, you need to insert the following information:

  • Server
  • Database (Cube Name)

Once entered and assuming the connection is fine you need to accept some native queries. These statements are select statements and will not alter the cube. That should give you a report similar to the one below, I have used an adventure works tabular model as an example.

Click here to download the report

This report is inspired by one I found for general tabular cube documentation by Data Savvy, which I highly recommend. That report includes information on: tables, columns, relationships, measure and security:

Click here to view tabular cube documentation blog post

How to delay a Python loop

In this blog post, I will run through 3 different ways to execute a delayed Python loop. In these examples, we will aim to run the loop once every minute.
To show the delay, we will print out the current datetime using the datetime module.

1 – Sleep
The sleep function from Python’s time module pauses the Python execution by the number of seconds inputted. The example below pauses the script for 60 seconds.

The above script has a 60 second delay between the end of a loop and the start of the next. However, what if we wanted to execute the next loop 60 seconds after the start of the previous loop.
In other words, how do we start the loop at the same time every minute.

2 – Calculate Sleep
To do this we calculate how long the loop should sleep for.
We will print the datetime every time the minutes changes. At the start of the loop we lookup the number of seconds that have passed so far this minute.

The number of seconds that have passed this minute is calculated from date_time[-2:]. Subtracting this from 60 gives the length of time in seconds for which the loop should sleep for, to execute when the next minute starts.

Once the loop has slept for the required number of seconds, we lookup the datetime again and print it out.

3 – Task Scheduler
The previous two options are good for executing a loop a few times, ten in our case. If we wanted to execute a python script continuously without expiring, we could use the above examples with an infinite appending loop.
However, if one loop errors the script will stop. Therefore, we want to execute the entire Python script once a minute using an external trigger. This is where we can use Task Scheduler.

Task Scheduler can execute a python script from source but it is often easier to use a batch file. The batch file includes the location of the python application (python.exe) and the location of the python script (.py). For more detail on using Task Scheduler and batch files to run Python scripts, please see the following datatofish post – https://datatofish.com/python-script-windows-scheduler

Our batch file is:

To demonstrate Task Scheduler, I’m going to run the following Python code every minute.
This code uses Pandas to produce a blank CSV file, but the name of CSV file is the datetime the script was run.

These following screenshots show the triggers and actions used.

This produced the following CSV files, we can see that the files takes 1 – 4 seconds to create.

In summary we have seen three different ways to delay a Python loop, two using loops inside Python and one using Task Scheduler. All can be used depending on what kind of delay is best.

Sorting a Power BI table by multiple columns

A common request that is raised by clients is how to sort a table in Power BI by multiple columns, in the same way you can in Excel.
For a while, there was no way (at least no easy way) to do this until the Power BI March 2020 update.

I learnt this tip from the following YouTube video:
https://www.youtube.com/watch?v=ik0K1H9j2Uc
Full credit to Dhruvin Shah, check his video out.

Below I have a Power BI table displaying fruit sales, currently unsorted.

To sort the table by Fruit click on the column header Fruit.

The table is now sorted by Fruit in alphabetical order.
To add a secondary sort on Sales, hold the Shift key and click on the column header Sales.

The table is now sorted by
– Fruit name in alphabetical order
– Sales in descending order

Some extras to note:
– There is no limit on the number of columns that can be used to sort a table. Just hold the shift key and keep choosing columns.
– This feature is not available for matrices.
– To switch the sorting from ascending to descending or vice-versa continue to hold shift and click on the column header again.

Creating a Python function to calculate Pi

Pi is 3.14159 to 5 decimal places.
To work out Pi, we will be using Leibniz’s formula:
X = 4 – 4/3 + 4/5 – 4/7 + 4/9 – …
This series converges to Pi, the more terms that are added to the series, the closer the value is to Pi.
For the proof on why this series converges to Pi – https://proofwiki.org/wiki/Leibniz’s_Formula_for_Pi
There are several points to note about the series:

  • It’s infinite, we need to find a way to continue adding term after term.
  • The denominator of the fraction increases by 2 every term.
  • The terms alternate between positive and negative.

Firstly, let’s create a function called pi.

To continue adding terms, let’s use a for loop. Every time the loop executes another term is added.

range(1,10) will produce the numbers 1, 2, … 9, 10.
However, before the loops starts, our variables’ initial values need to be set.

The pi series will start from 0.
n represents the numerator of our fractions which is the constant 4.
d represents the denominator of our fractions which starts as 1.

d needs to increase by 2 every loop, let’s use sum equals to do this.
Pi will also use a sum equals, a denotes our positive/negative function which we will get on to:

The only problem left is how to get a to alternate between 1 and -1.
This is where we introduce modulo.
Modulo outputs the remainder of a division and is denoted by %.

This example shows from numbers 1 to 4, modulo 2 alternates between 0 and 1.
If we multiple by 2 and minus 1 it will alternate between 1 and -1 which is what we require.

Putting everything together gives:

This isn’t close to 3.14159 at all.


However, we are only executing the loop 10 times, hence only 10 terms are being used to calculate Pi.

Increasing the number of loops to a million will change this:

There it is! A function written from scratch to calculate Pi.
To get a value even closer to Pi just increase the number of loops.

Finally, if you do wish to use Pi in python the easiest way is to use the numpy library, which has a pi constant stored.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon