Power BI – Using Reports Usage Metrics to create Custom Reports & Leverage them for other App Workspaces

In this blog post below I am going to show you how I created my own Custom Usage Metrics Report.

NOTE: This has been done using my Report Metrics and not the Dashboard Metrics.

NOTE II: I am going to refer to the Reports Usage Metrics as RUM (It has a nice ring to it)

And then how I changed the Connection details, so that I could re-use the same Custom Usage Metrics report in all my different App Workspaces.

(more…)

BI-RoundUp – Power BI (Desktop Update for June, Service Updates for June, Dashboard Email Subscriptions) – Excel (Get & Transform Updates June 2017)

Here is my weekly BI-RoundUp with what has happened and what is new.

Power BI – Desktop for June

Finally the Power BI Desktop July update is here. So here are the details below of the new updates.

Reporting

The table and matrix visuals are now generally available.

Not only that but they have also made some additional enhancements which are you can now drill down on columns or rows.

There is also an update to the default style which makes it easier to read based on best practice.

As well as finally they honor the word wrapping if you have text that goes onto a second line, it will keep it on that second line.

Next is the ability to rename the fields in your reports. When renaming your fields it creates an Alias so your existing fields that you get from your data will remain the same.

There now also is the Custom Visuals store integration directly in the Power BI Desktop. I think that this is a much better experience instead of having to go to the browser and open to find a custom visual. As well as now you can also get your Custom Visual Certified which will mean that it is verified by Microsoft Custom Development team.

There are also new Custom Visual updates which is in the blog post. I am interested into looking at the Drilldown Player.

You can now also use the Relative Date Slicers in the Filters section in Power BI Desktop. I use the Relative Date slicers quite often so it is great to see the same functionality in the Filters section also.

Next is the responsive layout for Visuals. This is another amazing feature in that the layout will respond for your visual based on how large or small it is. This means that it will show the information you are trying to portray in the most efficient way possible. And they can currently be used on the following Visuals. And that you can disable this responsive layout if required.


 

 

 

 

 

 

 

 

 

There is a new waterfall option where you can specify the breakdown which then show you that breakdown field causing the increasing of decreasing between each neighboring categories.

Analytics & Reporting

This month there are two new Quick Measures that have been added, which are the Star Rating and Concatenated List of Values.


Bidirectional cross filtering for DirectQuery is now Generally available also.

Data Connectivity

The Snowflake connector is now Generally Available also.

Query Editing

There have been some additional enhancements to the “Add Columns from Examples” in the Query Editor. In this months update they have added support for Number and Date/Time/TImezone data.

Also you can reference their documentation article which will be updated in due course here: Add a column from an example in Power BI Desktop

Blog Link

You can find all the blog details with a YouTube video here: Power BI Desktop July Feature Summary

Power BI – Service Updates for June

There have been quite a few updates to the Power BI Service in the last month.

Some of the highlights are now the new unified API, which you can now use not only for API access, but also for Power BI Embedded.

There is also an update in the Dashboard tiles for the relative links which enables you to direct the tile to go to a specific report. As well as the Impala connector.

Also there is an updated version for the Personal Data Gateway, which now will enable you to delete it from the Power BI Service, as well as enhancements for speed and reliability.

And finally they have made some performance improvement for the Power BI Mobile App.

You can find the details here: Power BI Service and Mobile June Feature Summary

Power BI – Dashboard Email Subscriptions

You can now subscribe to daily emails on your Dashboards. This is really an awesome new feature so that you can now get a daily snapshot of your dashboard emailed to you.

More details are here: Introducing dashboard email subscriptions: a 360-degree view of your business in your inbox every day

Excel – Get & Transform Updates June 2017

It is great to see that there are a whole host of new features in the Get & Transform updates in Excel. As mentioned previously these features are often enabled in Power BI Desktop, so if you are looking for some additional help or documentation include Power BI in your search to find solutions.

Here are the new updates for June 2017

  • New transformation features:
    • Add Column by Example.
    • Split Column (by Delimiter/Number of Characters) into rows.
    • Basic mode for Group By operation.
    • Extract Text Before/After/Between Delimiters.
    • Unpivot Only Selected Columns.
  • Combine Files experience—ability to reference first file as example.
  • New Go to Column in Query Editor.
  • SAP HANA Connector—enhancements to Parameter Input UX.

As you can see above there are quite a lot of new features, I have been using the Columns by Example since it has been released and I find that it not only enables me to get the data that I want quicker. But it also enables me to learn new M functions that I could potentially use in other transformations. The Split Column update is also handy when you need data to be split into rows. As well as the Extract Text before or after or between delimeters as I have found often that I want something inbetween.

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

BI-RoundUp – Power BI (Office 365 Adoption Content Pack – Embedded Accelerator) – Excel (Get & Transform Updates May 2017)

This will no doubt be a quieter week, leading up towards the end of May and into June when there will be the next wave of updates from the Microsoft Power BI Team

You might also notice that I have changed the name from BI-NSIGHT to BI-Roundup, as there was another blogger who has his blog URL as BiInsights, so rather than make it confusing I thought to change the weekly update name.

(more…)

Power BI – Free vs Pro Infographic

I have been active on the Power BI Community Page for quite some time, and what I have often seen is people not quite sure what options are free and what options require a Pro license.

So here is my infographic in which I have put down which options are free and which options will require a Pro license.

I am hopeful that people will find this useful in understanding which options are free and which options require a Pro license.

If I have left anything out, or something is wrong please let me know and I will update it.

As well as I will keep this infographic up to date as there no doubt will be some additions to the Power BI Service.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You can access the images from the following link: Power BI Free vs Pro Infographic – Latest Version

Power Query – Adding Parameters within a piece of text

Below is where I had a situation where I wanted to put in a parameter within a piece of text. This was so that I could then dynamically change the Month Version for my budget and when I refreshed my data it would then use my specific Month Version. This was due to the requirement being that they wanted the ability to select ANY Month Budget Version.

Example

  • I had a requirement where I wanted to use a parameter value, but it would form part of a complete part of text. And as shown below in this example it would be for Mar (March)
  • I wanted the Output to look like the following:
    • Budget_Mar_YR1
  • And the part which is part of the variable is highlighted in BLUE above “Mar
  • I also wanted to re-use this for multiple conditions later in my script.
    • I required it for the following:
      • CY – This is for the Current Year
      • YR1 – This is for the following Year 1, so if I am in 2016 it would be for Year 2017
      • YR2 – This is for the following Year 2, so if I am in 2016 it would be for Year 2018
      • YR3 – This is for the following Year 3, so if I am in 2016 it would be for Year 2019

Solution

In order to do this, I went into the Advanced Editor in the Query Editor.

The name of my Parameter was calledBudget Version“, so when using it in the Advanced Editor it would be used with the following syntax below.

#”Budget Version”

In the section below is where I now defined by additional conditions, so that they would be dynamic. An explanation will follow afterwards.

BudgetVersionCY = “Budget_”&#” Budget Version”&“_CY”,

BudgetVersionYR1 = “Budget_”&#” Budget Version”&”_YR1″,

BudgetVersionYR2= ” Budget _”&#” Budget Version”&”_ YR2″,

BudgetVersionYR3= ” Budget _”&#” Budget Version”&”_ YR3″,

As you can see above each line was compromised of the following:

  • I defined our name highlighted in RED
    • BudgetVersionCY
  • Then I started with what our name was, which is highlighted in BLUE
    • “Budget_”
  • Next is where I inserted our Parameter highlighted in PURPLE
    • &#” Budget Version”&
    • NOTE: When you want to add additional TEXT or parameters you have to open it with the ampersand “&” as well as close it off (or end it) with an ampersand “&” also.
  • And then finally I added some more text at the end highlighted in BLUE again.
    • “_CY”

Then later in my query is where I put in my conditional statements into my Conditional Column as shown below.

#”Filtered Rows” = Table.SelectRows(#”Added Custom2″, each ([Budget Version] = #”
BudgetVersionCY
” or [Budget Version] = #” BudgetVersionYR1” or [Budget Version] = #” BudgetVersionYR2” or [Budget Version] = #” BudgetVersionY3“)),

Final Note

Just one thing to note, is when I put in the following syntax into my Query Editor you will lose the capability to edit it by using the settings or Gear icon

BI-NSIGHT – Power BI (Microsoft Flow & Power BI, Webinar How Microsoft Handles Power BI)

I have no doubt that as the year moves closer to the end of the year there will be slightly less news and updates. But with that being said here are this weeks updates.

Power BI – Microsoft Flow and Power BI

I have been personally looking at Microsoft Flow lately and it can really automate many tasks, as well as do things for you. And it is very easy to use Microsoft Flow.

And now they have added the Data Alerting from Power BI into Microsoft Flow. This is really awesome, because what it now means is that you can leverage all the capabilities from Microsoft Flow when there is a data alert.

For example you can now forward the alert to an email group. Or do a whole host of other options in Microsoft Flow.

You can read all about it here: Turn insight into action using Microsoft Flow and Power BI

Power BI – Webinar on How Microsoft Handles Power BI

This looks to be an awesome Webinar and one that I will definitely be watching. It is going to be great to see how they go about making it work so well.

You can find the details here: Power BI Webinar 11/29 How Microsoft BI Team manages Power BI

Power BI Quick Tip – Using Parameters in Power Query Filters

I have been working lately quite a bit in Power Query and having to shape and re-create data based on the requirements.

They had a requirement to make the data dynamic and immediately the Parameters in Power BI came to mind to create the solution.

Now as we know using Parameters in Power BI has a lot of advantages.

But one quick tip that I want to share or highlight is that your parameters can be used in quite a few places in Power Query.

And in this tip, it is using your parameters to dynamically filter data in Power Query.

By doing so, your data that loads will then be dynamically driven by what you specify in your parameter.

Example

Here is an example below.

I created a parameter called Year Version (Which is the last 2 digits of the year).

I gave it the type of Decimal Number.

I then gave it a number of 18. As shown below.

Next I went into my table and went to my column called Data Year that I wanted to dynamically filter my Data Year with my parameter.

I think clicked on the column and selected Number Filters, then Greater Than Or Equal To

This then brings up the Filter Rows Window

Now the magic of the tip is in this next section below. If you look at below, it appears to me that your ONLY option is to put in a value.

BUT if you click on the drop down you now get 3 options!

Now I clicked on the option and changed it to Parameter. I then selected my Year Version parameter from above.

And now my data only show data after and including Data Year 18. And if I change my parameter, it will then dynamically
update my filtered data.

Power BI Service – How to disable Analyze in Excel (Disable Users from Downloading any data from Power BI)

I was recently looking how to help out in the Power BI Community where there was a question around how to disable users from having the ability to disable Analyze in Excel in the Power BI Service.

This could be a very valid situation where you do not want users the ability to export or download any data from the Power BI Service, but rather only have a view of the data, this is how you can achieve this. This includes the Export Data Option on the visuals.

NOTE: When you make this change it does the change for your entire Tenant. So please be aware of this.

A work around might to be enable Row Level Security, so that only the users who have access to the data can see the data. The result would then be when a user uses the Analyze in Excel and they browse the data, they will get data returned based on their security.

Change Tenant Settings

The first thing is that you will need to have access to the Admin Portal in the Power BI Service.

To see if you are an Admin you should see the option below.

Click on Settings to see the list of settings options.

If you can see the Admin Portal, click on the Admin Portal.

Now once in the Admin Portal you will need to click on the Tenant settings as shown below.

Next scroll down and change the following options:

  • Export data to Off
  • Allow users to use Analyze in Excel with on-premises datasets to Off

This is shown below.

NOTE: You have to have to both settings above set to Off for the Analyze in Excel option to be removed.

Then click Apply at the bottom of the page.

After you click Apply you will get a notification letting you know that it will take typically 5-10 minutes for the changes to take effect.

Once the 5-10 minutes is up you can then go back into your reports or datasets and you will see that the option for Analyze in Excel is now longer there.

Conclusion

So by changing the Tenant settings as explained above, your users will no longer have the ability to export any data from the Power BI Service, either by using the Export Data option on a visual, or the Analyze in Excel option on the Reports or Datasets.

BI-NSIGHT – Power BI (Mobile Apps Update, Custom Visual Tool Development Update, Dynamic Security Cheat Sheet, JavaScript API, Infer Content Pack) – Microsoft Flow (Available to Everyone) – SQL Server Analysis Services 2016 (Tabular Editor)

Another great week in the world of BI.

Power BI – Mobile Apps Update for August 2016

Another month goes by and some more updates, this time from the Mobile Apps team.

In this update they have done quite a lot of work with regards to the ability to do manual refreshing of your data in iOS if you have a direct query.

The ability to use your favourite dashboard on all platforms, which is very handy when you have your selected items that you want to view, quite possibly from different data sets, but in one easy to view dashboard.

They have also made the data classification visible if you have enabled your data in the mobile app, so that the users are aware in terms of what the status is if the data they are looking at.

The new focus mode for iPad which makes it easier to view the data you want to very easily.

And finally the ability to create data driven alerts from within the Mobile App.

You can find all the details here: Power BI Mobile Apps feature summary – August 2016

Power BI – Custom Visuals Tool Development Update

I am not a custom visual developer myself but having recently worked with some developers it is great to see the updates and additions that they have added. I have no doubt that this will make the custom visual development a lot easier and extensible as we move forward.

You can find all the details here as to what has changed, updated and added: Custom visual developer tool now Generally Available

Power BI – Dynamic Security Cheat Sheet

Kasper De Jonge, has created another great blog post in which he shows how to create and use Dynamic security in Power BI.

He explains exactly how he did it, and even has a working example that you can download and try.

Here is the blog post with the details: Power BI Desktop Dynamic security cheat sheet

Power BI – JavaScript API for Power BI reports.

The guys from the Power BI team are a very busy team. And now they have released the Javascript API which will enable you not only to embed your Power BI report into an iFrame but also enable your page to interact with the Power BI reports.

You can find all the details here: Interact with Power BI reports using the JavaScript API

Power BI – Infer Content Pack

Here is another content pack and I am sure if you are existing Infer client then this will be a very welcome addition.

Not only that but in terms of what Infer does in providing predictive technologies, I am sure that over time the dashboards will become more valuable to your business.

You can find all the details here: Explore and Analyze your Infer data with Power BI

Microsoft Flow – Available to Everyone

I have played around with Microsoft Flow and it is a great product and really easy to use when you want to automate tasks, as well as possibly get data from a site, OData feed etc and store it in a location. This is very easily achievable with Microsoft Flow.

They now have made it Generally available to everyone, even if you do not have a work or school account.

You can find the details here: Microsoft Flow now available for everyone

SQL Server Analysis Services 2016 – Tabular Editor

For the people using SQL Server Analysis Services in compatibility mode 1200 there is a great blog post, as well as details about the application that Daniel Otykier has created.

It makes it a lot easier and simpler to create and use Tabular Models, now that it uses the new JSON format.

You can find all the details here on what he has done. It does look very promising indeed.

A new way to work with SQL Server Tabular Models: The Tabular Editor