Data Insights Summit Details – Day 1 Summary

With the Microsoft Data Insights Summit that is currently happening in Seattle the Power BI team has been really busy letting us know what features are coming and what to get excited about in the coming months, so below is a quick overview of what has been released after Day 1.

What is coming

As you can see below there are a whole host of details around what is now available and what will be completed in the next quarter.

Drill through Pages

This is one of the announcements that have created quite a bit of excitement, in that very soon you will have the capability to be able to drill through to other pages. But not only that when you do drill through it will remember what you selected or filtered and use this on your drill through page. So now you can drill through, as well as go back to your previous page.

I personally have been asked about this before and I know that it is something that people have been waiting for. As well as this can make the entire reporting experience a lot better and interactive.

Organizational Folders

It appears that you will now be able to organize your folders as you could previously do with SSAS OLAP

Power BI Write back with Power Apps and Visio Custom Visual

This is a big one, where they have now used Power Apps in order to facilitate a write back of your data into your Existing Power BI Model. This is something that people have been asking for, for a long time.

As well as now you can also integrate Visio visuals into your Power BI report, and if you create your Visio visuals with ID’s you can map these to your data so that they will interact as you can currently do with all other visuals within your report canvas.

Azure Analysis Web Designer

There is a preview coming where you can now design and tune Azure Analysis Services from within your Web Browser.

The things to take a note of is that you can import Power BI Desktop files which will be converted into Azure Analysis Services, which means you can create a proof of concept locally and then enable it to be used for the enterprise.

As well as then integrate these datasets with Power BI, Excel, Visual Studio and GitHub, so if you are looking for more source control around your development this might be the option going forward.

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.

Power BI – Office 365 Adoption Content Pack

There is a new content pack for Power BI, which will leverage off of the Office 365 reporting, so that you can gain some insights into how your organization is adopting Office 365.

You can find more details here: Explore your Office 365 Adoption Data in Power BI

Power BI – Embedded Accelerator

If you are looking to use Power BI Embedded, Microsoft have a limited offer to help you get up and running.

You can find the blog post here: Power BI Embedded Accelerator

Excel – Get & Transform Updates May 2017

While the features below have been available in Power BI, it is great to see them also incorporated into Excel, as these are some really useful updates.

The first one is the auto selection of the delimiter when using the Split By, I have been using this for a while and whilst it does not appear to be time consuming it is awesome to have it pre-selected for you.

The second one is when combining files to be able to select which sample file to use from a folder.

And finally you can now connect to DB2

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

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.