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