Power BI – Create Last Dataset Refresh Date

Whilst I have read and found other ways to get the Last Dataset Refresh Date, I find that my method is a lot easier and cleaner to complete.

Creating the Last Dataset Refresh Data

Open Power BI Desktop and go into the Query Editor.

Then click on New Source and select Blank Query

Next I renamed my Blank Query from Query1 to Last Dataset Refresh

Next click on the Advanced Editor and put in the following M Code, which I will explain what it does after the code.

let

TodaysDate = DateTime.From(DateTimeZone.SwitchZone(DateTimeZone.FixedLocalNow(),10)),

#”Converted to Table” = #table(1, {{TodaysDate}}),

#”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Last Refresh Date Time”}}),

#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Last Refresh Date Time”, type datetime}})

in

#”Changed Type”

What the above is doing is it is getting today’s Date and time. And this is where I created the magic on the first line.

If you have a look I have put in the Timezone that I am in. I currently am living in Brisbane which is +10 from GMT.

So as you can see from above I have put in 10 for the DateTimezone.SwitchZone, highlighted in BLUE.

The rest of the code converts it to a table, and then I rename the column and finally give it the data type of DateTime.

So the result is the following I created shown below.

I then loaded this into my Power BI Model.

Using the Last Dataset Refresh in your Power BI Reports

Now what I did was I had to create a Calculated Measure, so that I could then use the Last
Dataset Refresh Date.

Below is the Syntax that I used to create my Calculated Measure called Data Last Refreshed

Data Last Refreshed =

VALUES ( ‘Last Dataset Refresh'[Last Refresh Date Time] )

Finally I put the measure onto my report and configured it with the Card Visual and the following properties shown below.

I did this to ensure that it would take up the least amount of screen real estate as possible.

And I test it by publishing it to the Power BI Service and it displayed the correct Date and Time.

Conclusion

As you can see this is a simple and effective way to shown when the data in your Power BI Report was last updated.

Any questions or suggestions please feel free to let me know.

BI-NSIGHT – Power BI (Design your own Swag, Custom Refresh Schedules for Live/DirectQuery, Report Theme Gallery, Custom Visuals Community Site, Filter a report with a URL query string, Summit EMEA, Custom Visuals in Office Store, Data Insights Summit Session Details

Once again the world of BI is very busy with great new features and other details which I have put into this blog post below.

Power BI – Design your own Swag

There is a competition to design your own Swag Shirt for Power BI. As you can see above is my entry, so if you like it, please go and vote for it here: Power BI – Create – Collate – Visualize & Explore

As well as look at the other entries here:

Power BI – Custom Refresh Schedules for Live/DirectQuery

I have to say I have noticed that the Power BI team are currently releasing a lot of features which give a lot more granular control to different things in the Power BI Service. From my point of view this is fantastic as it allows more flexibility in the product, which means it can potentially be released to a wider audience in some instances.

This latest release with regards to the Custom Refresh Schedules for Live/DirectQuery is another great granular feature. This allows the user to now control how often to refresh the cash, which is used for the Dashboards tiles. And can help ensure that it can be optimized.

You can find the blog details here: Announcing Custom Cache Refresh Schedules in the Power BI Service

Power BI – Report Theme Gallery

Due to the release of the preview feature of Report themes into Power BI Desktop, the Power BI Team has now release a Report Theme Gallery.

This is where you can showcase your theme that you created, as well as share it with others who might want to use it.

I personally think this is great, and once again shows how Power BI is a community where we can share our knowledge.

Here are the details: Power BI Community Report Theme Gallery

Power BI – Custom Visuals Community Site

Here is yet another great initiative from the Power BI team, and this is all around the Custom Visuals where they have created a Custom Visuals Community site.

This will be a great resource for people who are looking to develop Custom Visuals as well as assist other people with issues.

You can find all the details here: New custom visuals community site: developers and users unite!

Power BI – Filter a report with a URL Query String

Apparently this has been in the documentation for some time, but was only publically shown now by Adam Saxton (Guy In a Cube).

In the blog post they show how you are able to filter reports in the URL using a Query String.

This functionality used to exist in Power View for SharePoint and I can remember using it in various scenario’s where you wanted the report to automatically filter. This is great to see that you can now do this in Power BI also.

You can see Adam’s video and blog post details here: Filter a report with a URL query string parameter

Power BI – Summit EMEA

If you are in the EMEA region I would suggest if possible attending the Summit EMEA, where you can learn about Power BI, as well as discover other details around Power BI and the various insights it provides.

You can find the details here: Dive into Power BI at Summit EMEA

Power BI – Custom Visuals available in the Office Store

It is interesting to see that the Custom Visuals are going to be moved to the Office store. In a way I can understand this, but it also makes me lead to think that in the future there is the possibility to potentially pay for Custom Visuals?

I am sure we will find out sooner or later.

Here is the blog post details: Custom visuals now available in the Office store

Power BI – Data Insights Summit sessions

You can now get access and see all the sessions that are going to be available at the Data Insights Summit session.

I see that there will also be a pre-con day on 11 June 2017. Loads of great content

Here are the details: The Microsoft Data Insights Summit is back – check out our full session catalog today!

BI-NSIGHT – Power BI (Custom Refresh Schedules for Live/DirectQuery, Report Theme Gallery, Custom Visuals Community Site, Filter a report with a URL query string, Summit EMEA)

Once again the world of BI is very busy with great new features and other details which I have put into this blog post below.

Power BI – Custom Refresh Schedules for Live/DirectQuery

I have to say I have noticed that the Power BI team are currently releasing a lot of features which give a lot more granular control to different things in the Power BI Service. From my point of view this is fantastic as it allows more flexibility in the product, which means it can potentially be released to a wider audience in some instances.

This latest release with regards to the Custom Refresh Schedules for Live/DirectQuery is another great granular feature. This allows the user to now control how often to refresh the cash, which is used for the Dashboards tiles. And can help ensure that it can be optimized.

You can find the blog details here: Announcing Custom Cache Refresh Schedules in the Power BI Service

Power BI – Report Theme Gallery

Due to the release of the preview feature of Report themes into Power BI Desktop, the Power BI Team has now release a Report Theme Gallery.

This is where you can showcase your theme that you created, as well as share it with others who might want to use it.

I personally think this is great, and once again shows how Power BI is a community where we can share our knowledge.

Here are the details: Power BI Community Report Theme Gallery

Power BI – Custom Visuals Community Site

Here is yet another great initiative from the Power BI team, and this is all around the Custom Visuals where they have created a Custom Visuals Community site.

This will be a great resource for people who are looking to develop Custom Visuals as well as assist other people with issues.

You can find all the details here: New custom visuals community site: developers and users unite!

Power BI – Filter a report with a URL Query String

Apparently this has been in the documentation for some time, but was only publically shown now by Adam Saxton (Guy In a Cube).

In the blog post they show how you are able to filter reports in the URL using a Query String.

This functionality used to exist in Power View for SharePoint and I can remember using it in various scenario’s where you wanted the report to automatically filter. This is great to see that you can now do this in Power BI also.

You can see Adam’s video and blog post details here: Filter a report with a URL query string parameter

Power BI – Summit EMEA

If you are in the EMEA region I would suggest if possible attending the Summit EMEA, where you can learn about Power BI, as well as discover other details around Power BI and the various insights it provides.

You can find the details here: Dive into Power BI at Summit EMEA

Power BI – Gateway Infographic

Following on from my previous Power BI Infographic blog post, I also have had the idea to create an Infographic for the Power BI Gateway.

This came about after reading the blog post by Kasper De Jonge Enterprise gateway sizing “recommendations”, and there was a lot of valuable information which I have put into the Infographic as well as detailed further below.

One of the main takeaways that I learnt from this was that I got a better understanding not only of how the Power BI Gateway works, but also how the Power BI Service works and consumes data.

Very simply put, the Power BI Gateway is currently responsible for 3 things.

  • The Query Editor is where the processing takes place when you refresh your data, the Query Editor Engine is actually built into the Power BI Gateway. So if your dataset uses the Query Editor for any data shaping, then this is done within the Power BI Gateway.
  • The Live/Direct Query is used to do live connections or direct queries against the various sources as shown below.
  • The final and very important piece is the routing of the data between your on-premise network and the Azure Service bus, which then connects to the Power BI Service.

The takeaway for me from this was that firstly the Power BI Gateway actually has the Query Editor engine built in.

Secondly was that currently within the Power BI Service, it consumes the data that is already shaped into the required tables. And this would explain why currently when you import data directly, what you can do with the data is very limited.

Power BI Gateway Infographic

Page one is the graphical overview of the Power BI from an end to end view.

You can access the images from the following link: Power BI Gateway Infographic – Latest Version

In this section below I summarize from Kasper’s Blog post, if you are interested in more details around the Power BI Gateway, then please read below.

Cached Data

  • Data is collected from Source.
    • Ideally the connection between your On-Premise Gateway and your source is as fast as possible.
    • This also includes the least amount of hops as possible.
  • It then goes through the Power Query Engine (which is part of the On-Premise Gateway)
    • So if it is using queries, where the transformations happen in the source system it will not use many resources.
    • But if it does all the work in the Query Engine, then it will require CPU & Memory to do the transformations and shaping of the data.
  • Once it is in the correct shape, it then needs a fast connection to transfer from the On-Premise Gateway to the Power BI Service.

Live Connect or Direct Connect

  • The only data that gets moved here is the data required to render the visual.
  • The On-Premise Gateway acts as in a pass along mode, passing the data to the source, getting the results and passing it back to the Power BI Service.
  • There is some CPU used for routing, but not a significant amount.
  • To ensure that this is as seamless as possible the connection needs to be as fast as possible.
    • So reducing any additional hops will possibly make the experience quicker.

Sizing – It depends

  • How many data models are going to be refreshed concurrently?
  • How Power Query dependent / intensive is the data sources?
    • If it is straight forward connections (To SQL Server, DDBMS systems)?
    • Or is all the work done in the Query Editor?
    • The more Cores the better, as well as memory if the current models when being refreshed in Power BI Desktop are memory instensive.
  • How much data is being moved?
    • The more data that is being moved, the more cores required for routing.
  • How many users will be using Direct Query or Live Connect?
    • If the underlying models use Row Level Security (RLS), as this will then require separate queries per user.

Monitoring On-Premise Gateways

It is essential to monitor the On-Premise gateways to understand how the business is using their data sources, as well as the usage on the actual server.

You can use the documentation described here: Troubleshooting the On-Premises Data Gateway

And then the section Performance Counters

Enterprise Solution

This might be required if you have got a large user base and want to have specific data refreshed as a priority

  • Create additional dedicated On-Premise Gateway Servers.
    • Have one for Corporate wide reports, used by BI Team and CFO and C’s
    • Have a second one which can be used by your business users to refresh their ad-hoc reports.
  • This will ensure that you can isolate any issues or errors.

BI-NSIGHT – Power BI (Granular Tenant settings, Amazon Redshift connectivity in Power BI Service, Featured Data Stories, Availability Monitoring Publish to Web) – Excel (Get & Transform updates) – SQL Server (DAX support in Report Builder and SSMS)

Here are the weekly updates.

Power BI – Granular Tenant Settings

I can personally say that I know a few of my clients who are very happy that the Power BI team has already implemented the Granular Tenant settings for Power BI. I have to say that I am once again impressed by the Power BI Team really a great job.

I also like the fact that they have gone one step further and put in the option for “Except specific security groups”, because so often you want to allow it for almost everyone, except one group. And with their implementation they make this really easy and simple to implement.

Currently as at 16 Mar 2017, the granular settings are for the following:

  • Export and sharing settings
    • Share content to external users
    • Export Data
    • Export reports to PowerPoint presentations
    • Print dashboards and reports
  • Content pack settings
    • Publish content packs to the entire organization
    • Create template organizational content packs
  • Integration settings
    • Use Analyze in Excel with on-premise datasets

You can read the blog post here: Announcing granular tenant settings in Power BI

Power BI – Gateway Update for March

As you can see above the Gateway for Power BI has had a whole host of updates in the March 2017 release.

In this release there is now an updated UI (User Interface), where you can now see the status of the Gateway.

You also have the options under the Service settings to restart the service, as well as change the Gateway Service Account.

In the Diagnostics section, you can now enable verbose logging, as well as export all the gateway logs to one zip file.

And finally under the Network section there is the capability to view the Local network status, as well as modify the Azure Service Bus connectivity mode.

There is also an update for the Gateway to use a FQDN instead of a list of IP addresses so that you will no longer be required to keep a list of IP Addresses.

Also if you are looking to use an Oracle data source with Kerberos configured the Power BI Gateway team are looking for candidates.

You can find the blog post details here: Power BI Gateways–March Update

Power BI – Amazon Redshift in Power BI Service

This is a informative blog post by the Power BI team where they explain how to connect, and publish your Power BI reports using Amazon Redshift.

You can find the details here: Announcing support for Amazon Redshift connectivity in the Power BI service

Power BI – Featured Data Stories

Congratulations to David Eldersveld on his featured data story, really a great way showcasing Power BI as well as the other people featured.

Next month’s feature requests are tables and matrices.

You can find the featured data stories here: Congratulations to this month’s Featured Data Stories

Excel – Get & Transform Updates

There are two updates to the Excel Get & Transform updates in the March 2017 update. The first is a new transformation which allows you to extract data values from a column. And the second is where you now have support for SQL Server Failovers when using a SQL Server database connection.

You can find the details here: March 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Availability Monitoring Publish to Web

Charles Sterling from the Power BI team has a great blog post if you want to monitor the availability and ensure that your Publish to Web is working. He shows this in a few simple steps.

You can find the details here: Setting up availability monitoring for Power BI Publish to Web Results

SQL Server – DAX support in Report Builder and SSMS

In the latest release of Report Builder and SSMS (SQL Server Management Studio) there now is the ability in the query designer for DAX.

You can find all the details here: Query designer support for DAX now available in Report Builder and SQL Server Data Tools

Power BI Query Editor – Getting IP Address Details from IP Address

I recently had a question from a user in the Power BI Community page who wanted to know where the people were coming from based on their IP Address. The IP addresses were stored as part of the dataset, but to try and go and do this with the IP Address database meant that you would then need to go and translate the IP addresses into a number, to cross reference across the IP Address ranges.

My solution below rather uses the web lookup, which will work using any dataset, as well as simple and easy to use.

Adding the Function into your Query Editor

  • The first thing that you will need to do is to create the function which I did with the following steps below.
    • Click on New Source, and then select Blank Query
    • Next rename it from Query1 to fn_GetIPAddressDetails
      • You can do this by right clicking and select Rename
    • Next in the Home Ribbon under the Query section click on Advanced Editor
    • Now paste in the following Power Query (M) syntax
      let
      Source = (#"IP Address" as text) => let
      Source = Json.Document(Web.Contents("http://freegeoip.net/json/" & #"IP Address")),
      #"Converted to Table" = Record.ToTable(Source),
      #"Transposed Table" = Table.Transpose(#"Converted to Table"),
      #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table")
      in
      #"Promoted Headers"
      in
      Source
    • Then click Ok.
    • Now you should see the following for your function
  • What the above does is it takes the input of IP Address
  • Then what it does it takes the IP Address and then using the above service http://freegeoip.net looks up the IP Address and returns the details.
  • Next it converts the JSON to a table.
  • After which I then transposed the data.
  • And finally promoted the first Row as Headers

Using the Function with your Data to output the IP Address details

In this step I will now show you how to use this function to get the output from your IP Addresses, in your data.

  • I have used a sample file in which I made up the IP Addresses as shown below.
  • I then went into the Add Column in the Ribbon and clicked on Invoke Custom Function
  • This brings up the Invoke Custom Function window and I put in the following information as shown below.
    • As you can see from above, I gave my new column a name of Details
    • I then clicked the drop down and selected my function I created earlier called fn_GetIPAddressDetails
    • And then finally the crucial part is where I selected my IP Address Column.
    • I then clicked Ok.
  • When you do this it returns a table as shown below.
  • Click on the Expand Table Button on the top right hand side, which will then prompt you which columns you want to select
  • I left them all selected and clicked Ok.
  • And as you can see below, here is the first 3 columns from the list
  • I then loaded my data into my Power BI Model and created a map visual using ESRI

So in conclusion you can see it is very easy to use Power BI to create functions, which can iterate over a your dataset and give you a meaningful output with not a lot of effort, where in the past this used to take a significant amount of effort.

You can download the sample file here: Get IP Address Details.pbix

Power BI – (March Desktop Update, Theme’s Generator, Esri New Technology Integration Award, K2 Process Content Pack, Advanced Search Solution Template, Project Service Automation for Dynamics 365 Solution Template, New Custom Visual 3D Pie Chart)

There has been a whole host of updates to Power BI in the past week which is great to see, so I hope that you enjoy reading this as much as I enjoyed putting it all together.

Power BI – March Desktop Update

In this month’s updates there are a whole host of great updates, and the Power BI Team has been very busy. So I will go through each section below in what I think are important highlights.

Report View

This is where the majority of the updates and new additions are, which I really like because it means that there is now even more capability to customize my Power BI Reports.

The first addition is the ability to theme the reports. I know that this is something people have been asking for. And currently it does seem rather manual and the Power BI Team does speak to it in the YouTube video that this is the first version and that they will update it as the months go on. Already there is a Theme Generator by Charles Sterling for this, which you can find further down in my blog.

Next is the Preview Matrix, which has a lot of updates, one of which is performance improvements. As well as a whole host of formatting options (stepped layout, drilling into hierarchies) which enabled me to fit more data into the same area. I also liked the fact that you can now do cross filtering both from the Preview Matrix, as well as from other charts. Just remember that you need to enable this in the Preview Features to gain access to the Preview Matrix.

Even though this is something that appears quite small, I know personally in the past this is something that I wanted to change the color of the text.

Another very handy slicer is the new Numeric range slicer. The trick with this first version is that you can only use it on a Numeric column and not YET on a Calculated measure. But it does make it quick and easy to slice the data.

I have actually been asked in the past if I was able to put in the percentages on Pie or Donut charts, and now the Power BI team has released this functionality with all the different options.

And finally in the reporting section is the ability to Cross-highlight using multiple series. So this means that I now can select anything combination in my series and it will show the results.

Analytics

Under analytics it is good to see that Clustering is now generally available.

Data Connectivity

In terms of data connectivity it is good to see that you can now connect to the additional sources below.

  • Azure Analysis Services Connector
  • Azure Data Lake Store
  • DB2 Connector – Option to specify package collection
  • Combined Binaries – Gives you the option to select the sample file.

Query Editor

And finally in the Query Editor there now is automatic detection for the delimiter character when you split a column. You can still change this if the automatic detection is not right.

You can find the blog post here: Power BI Desktop March Feature Summary

Power BI – Theme’s Generator

Following on from the update of Power BI Desktop for March and the ability to create theme’s Charles Sterling from the Power BI team as released a very easy way to create the themes that you require.

Click on this link http://thememaster500.azurewebsites.net/

Once there you get the image as shown above. You can then select the color pallet that works for you.

Then click Apply and it will download the JSON file, for you to import into Power BI.

Power BI – Receives the ESRI New Technology Integration Award

I think it is great when other providers acknowledge the work done by the Power BI Team. And here is an award from Esri New Technology Integration Award for 2017.

You can read the blog post here with all the details: Microsoft Power BI receives the Esri New Technology Integration Award for 2017

Power BI – K2 Process Content pack

This is another really good example from Power BI and K2, so if you are an existing K2 customer this is a very quick and easy way to gain insights into your data.

You can find the blog post here: Explore your K2 Process Analytics Data with Power BI

Power BI – Advanced Search Solution Template for Bing News

Here is another solution template in which you can put in some search terms and the solution template will go and use the Azure services and create a report that will allow you to view sentiment, topics, organizing people and organizations into locations.

You can find the details here: Announcing the Advanced Search Solution Template for Bing News

Power BI – Project Service Automation for Dynamics 365 Solution Template

And here is yet another solution template this time for Microsoft Dynamics 365 for Project Service automation. This template will enable the business to get a set of reports for everything focused around the Project Service Automation in Dynamics 365

The blog post details can be found here: Announcing the Power BI solution template for Microsoft Dynamics 365 for Project Service Automation

Power BI – New Visual 3D Pie Chart

As you can see above there is another new Custom Visual, the 3D Pie chart available in the Power BI Custom Visuals Gallery

You can find the visual here: Pie 3D Chart

Power BI – Power Query/Query Editor Automated Source Control

I saw a blog post recently where Jay Killeen was talking about how he handles his Source Control for the Query Editor or Power Query. And this got me thinking of how I could automate this in a quick and easy manner. So below is how I achieved this.

My solution below, uses a BAT file and 7zip, which means that the process can be automated using a Scheduled Task, and be continuously running in the background.

All that you will need is 7Zip and command prompt (CMD).

Example

For this example, I will show how I get the DataMashup file from your Power BI Desktop file, whilst your Power BI Desktop file is still open.

Then take this file and copy it to OneDrive (You can potentially use this with any other systems such as Google Drive, Drobox etc.) with a Date Timestamp which will make the file unique, as well as know when it was created.

And finally, I will also show how you can revert to a previous version of the file if required.

NOTE: You can also use this process if you want to Share your Power Query/Query Editor files with other people. The people who require the changes must follow the steps in the Reverting back to a Previous Version of the DataMashup File

Requirements

Below you will need to download and install 7Zip (32bit) for this to work.

7Zip (32bit)

What is the Power Query/Query Editor File and where do you find it

I thought it would be good to quickly explain how I came to know about the Power Query/Query Editor file.

  • If you change the file extension of your Power BI Model from .pbix to .zip, this then enables you to view the contents that make up the .pbix file.
  • So as you can see below when I have renamed my .pbix file I see the following:
  • Now after I had been doing some investigations I found out that the file called DataMashup is the file that contains everything in the Power Query/Query Editor.

So this is the file that we need to use for our Source Control for the Power Query/Query Editor.

BAT File explanation

Below is the BAT file that I have created in which it is doing all the steps which are explained below.

NOTE: You will be required to change the following variables in the BAT File, so that it will work for your data.

  • Change this to the location of your Source Directory of your Power BI File

    set SD=”C:\Users\guava\Downloads\”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.
  • Change this to the location of your Destination Directory where you want your DataMashup file to be copied to

    set DD=”C:\Users\guava\OneDrive\BI\Power BI\Power Query Versions\Data Gateway Test”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.
  • Change this to the Filename of your Power BI Desktop File. NOTE: Do not include the extension.

    set PBIX=”Data Gateway Refresh Test”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.

Here below is the code from the BAT file which explains what each step is doing.

The final step is to save the BAT file to an appropriate File Name.

With my example, I gave it the file name of: PBISC – Data Gateway Test.BAT

How it works

Now once you have you updated the BAT file with the correct variables it is as simple as running it from the command line.

NOTE: You have to first save your Power BI Desktop file in order for the changes to be saved into the DataMashup file.

NOTE II: I would recommend that you put in the full File Path for your BAT file to ensure that it will always run
correctly.

  • I created a specific Folder Structure in my OneDrive Folders, so that I have a separate Folder per PBIX file.
    • As you can see it is currently empty.
  • Next I run my BAT file from the command prompt
  • Once it has run I see the following in command prompt:
    • As you can see above it is the output from the BAT File.
    • NOTE: You can also run this BAT File from the Run command, which will also run successfully.
  • And I now see the file in my OneDrive Folder.

Example of Saving Changes

So in the steps below I will now show after I have made a change in the Power Query/Query Editor how the change in both the file name and size is automatically copied and synced to my OneDrive Folder.

  • I have got my Query Editor open in Power BI Desktop
  • What I will now do is to duplicate the table called “Budget Data without New Year“, as well as disable the Loading of this dataset (This is because I want to show how the DataMashup file changes.).
    • As you can see above I have duplicated the table and Disabled the “Enable Load
  • I then click on Close and Apply, and then save my Power BI Desktop File.
  • Next I run my BAT file again
  • Now you can see in the picture below I have got my new file.
    • You can also see that the file size is slightly larger due to having duplicated
      my table in the Query Editor (which includes all the steps for the table)

Reverting back to a Previous Version of the DataMashup File

The whole reason for the automating the version control is so that in the event you need to go back to a previous version, we have the files required to do this. And I explain how to do this in the steps below.

  • The first thing that you will need to do is close your Power BI Desktop file.
  • Next go to the location of where you have stored the copies of the DataMashup files.
  • With my example, it is in the following location:
  • Now I took a copy of the file that I want to revert back to.
  • With my example I am going to revert back to the first file called DataMashup_02-03-2017_20_08_18 because this is the file that does not have the duplicated table.
  • I made a copy to my Documents Folder.
  • I then rename the file from DataMashup_02-03-2017_20_08_18 to DataMashup
    • NOTE: This is because in the Power BI file format it will look for a file called DataMashup
  • Next I go back to my Power BI Desktop file and rename it from Data Gateway Refresh Test.pbix to Data Gateway Refresh Test.zip
    • It will prompt you asking are you sure you want to change it?
    • Click Yes.
  • Now I went into the zip file by double clicking on the file Data Gateway Refresh Test.zip
  • Next I went back to my Documents folder and copied the DataMashup file.
  • I then went back to my zip file and then right clicked and selected Paste
    • This then prompted me with the following window as shown below.
    • I clicked on Copy and Replace
    • NOTE: This is so that it will replace the DataMashup file with my previous version.
  • Now I had to refresh the zip file to see the changes.
    • Which you can see below the file size has gone from 34k to 27k
  • Now I renamed my zip file back to the pbix file.
    • It will prompt you asking are you sure you want to change it?
    • Click Yes
  • I then opened my Data Gateway Refresh Test.pbix and went into the Query Editor
    • As you can see with the picture below I do not have my additional table
    • It is back to 7 queries.
  • I have successfully reverted to a previous version.

Conclusion

So in conclusion you can see how I automated the process of not only copying the DataMashup file, but also using a system like OneDrive I can automatically sync this data to the cloud and use the built in versioning control.

Here is the link below to the BAT file that I created, for anyone who wants to use it for their own automation and source control for the Power Query/Query Editor data.

I also asked a good friend of mine to create a PowerShell script doing the same thing. So if you want to use PowerShell instead of the BAT File please find the files below.

NOTE: For the BAT file the extension is txt-BAT and for the PowerShell script the extension is txt-ps1, this is so that it should be able to be successfully downloaded or emailed.

PBISC – Data Gateway Test.txt-BAT

Power BI DataMashup.txt-ps1

And as always if anyone has got any comments or suggestions please let me know. As I have a feeling that there might be some great ideas that come from this.

BI-NSIGHT – Power BI (Mobile Apps Update Feb, Connecting from Power BI Mobile to SSRS, Embedding Power BI Reports)

As another month comes to a close, I am excited to see what the next week will bring. Hopefully some updates for Power BI Desktop and the service. Here is this week’s roundup.

Power BI – Mobile Apps Update for February

In this month’s mobile update they have made some additional actions when interacting with the Dashboard tiles on the mobile app. Making it easier to navigate to an underlying report, expand a tile or even look at the alerts.

A great addition is you now can have multiple SSRS Servers in your app (Currently iOS)

And finally they have made some improvements with regards to scrolling across all the mobile platforms. Which makes it a lot easier to navigate with the charts when it can possibly be so small to navigate.

You can find the blog post here: Power BI mobile apps feature summary – February 2017

Power BI – Tech Tip Connecting from Power BI Mobile to SSRS

This is a great video by Adam Saxton aka Guy In a Cube in which he goes through how to connect from Power BI Mobile to SSRS using OAuth

You can find the details here: Tech Tip Thursday: Connecting from Power BI Mobile to SSRS

Power BI – Embedding Power BI visuals, tiles or widgets into your application

There is a great blog post from the Power BI team which goes into detail and explains how to very easily embed your Power BI visuals, tiles or widgets into an existing application.

Along with this, there is also a video by Guy In a Cube going through this, click on the image below.

Here are the actual blog details: Embed Dashboards in Your Applications to Monitor Your Business in Context

Power BI – Creating a Calculated Table which holds a particular single value

I was working on a Power BI Model where I had a requirement where I wanted to use the Last value from an [Actuals] calculated measure as part of another calculated measure [Pro-rata Budget Selection].

Now due to the way the filter context works, when I tried the following calculated measure below, it would return the value where the Fiscal Quarter was Q4, when what I was wanting to get back was Q2. And this was due to the filter context being applied differently across the different calculated measures.

CALCULATE (
[Pro-rata Budget Selection],
FILTER (
'Date',
'Date'[Fiscal Quarter]
  = LASTNONBLANK('Date'[Fiscal Quarter],[Actuals])

So in order to solve this issue I created a calculated table which would only have the value of Q2.

Solution

I created the table below so that subsequently every time the data is updated, the Fiscal Quarter would change based on the data.

So below is the syntax with the explanation afterwards.

  • First I clicked in the Modeling Ribbon and selected New Table
  • Next I put in the syntax
    Calculated Table - LastNonBlank =
    CALCULATETABLE (
    LASTNONBLANK ( 'Date'[Fiscal Quarter], [Actuals] ),
     'Date'[Current Year] = VALUES ( 'Current Year'[Current Year]
     )
    )
  • The important piece of DAX syntax is highlighted in LIGHT BLUE above. Starting with the LASTNONBLANK
    • As you can see I got the last non blank value for the Fiscal Quarter from my Date table based on the [Actuals] calculated measure.
  • Then I added in an additional filter in which I filtered for the ‘Date'[Current Year]
    highlighted in ORANGE
    • NOTE: The ‘Current Year'[Current Year] is simply taken my Parameter called “Current Year” and converted it to a list.
      • When you do this, it imports it into the Power BI Model, which makes it very useful for when you want to use it as part of other calculated measures.
  • So the output of this table is shown below.
  • As you can see above I now have a Text Value of Q2

So my original calculated measure now changed to the following below.

CALCULATE (
[Pro-rata Budget Selection],
FILTER (
'Date',
'Date'[Fiscal Quarter]
    = VALUES ( 'Calculated Table - LastNonBlank'[Fiscal Quarter] )

Conclusion

So this enabled me to have a value, which I could use in any calculation due to the fact that in the Power BI Model it is seen as a table value.

So that meant I could use it very easily as part of any calculated measure, as shown above.