BI-NSIGHT – Power BI (November Desktop Update, Mobile App Update)

I now expect if not on the last day of each month, but pretty close to the last day is to get the updates for the Power BI Desktop and potentially the Mobile Apps update. And this month I am not disappointed.

Power BI – November Desktop Update

Once again the Power BI Team has been very busy with a wealth of updates, as I traditionally do I am only going to highlight the ones that I think need to be highlighted. There are a lot of updates, so please be sure to read the blog post from the Power BI Team.

Below is all of the updates, so that you can see what they all are in case I do not cover them all.

NOTE: All the items below are clickable.

Report View

  • Analytics

  • Data Connectors

  • Query Editing Improvements

  • Reports View

    It is great to see that they are making not it easier to use the slicers, but also making it easier to read the information on charts when using hierarchies.

    Not only that but you can now also change the colours of the Axis Label and Title Colours for your charts.


    Along with this is all the additional conditional formatting that can be achieved in the Tables and Matrices which is shown with some images below.


    Analytics

    Every time I see an analytics update, I always am intrigued to see what feature or update they are completing this month. And this month the Power BI Team does not disappoint with the Clustering (Preview).

    I really like how they make it so easy to use the Clustering algorithm and it goes and does all the hard work for you. And not only that, but you can also add additional measures, as well as how many clusters you require.

    They have taken something that is quite complex and made it to appear to be fairly simple and easy to complete.




    Another quick note is that now Forecasting is available in the Power BI Service.

    As well as they made it easier to create groups from multiple places, as is often the case with Microsoft products is that there is more than one way to do the same thing.

    Data Connectors

    It is amazing to see how quickly they have added a connector for Azure Analysis Services, as I am sure that this is gaining quite a lot of momentum, as well as being a really great Microsoft BI product.

    Once again they have added another data connector for Spark, which I have no doubt the Big data, open source community will be happy with.

    As well as a whole host of additional improvements in the OData, Combined Binaries (CSV, Flat Files & Excel Files) and Web Connectors also.

    Query Editor

    This is the one piece of Power BI where I think it delivers so much value and they have improved it in this update with the Improved Function Authoring experience. Which makes it easier when working with functions.

    As well as add support for the % data type.

    And finally the ability to Expand the Query Dependencies view, which is a very welcome addition.


    You can read up all about it here: Power BI Desktop November Feature Summary

    Power BI – Mobile Update for November

    There are some really good updates for the Mobile App this month.

    Starting with the support for Intune Mobile Application management for Android devices. This is great to see as due to my mobile device being everywhere it can easily be lost or get into someone else’s hands. And by having the Intune capability it ensures that the devices are as secure as they can be with Power BI data.

    As well as support for links from within the iOS app.

    And the ability to centre a map to focus on your data in the Windows App.

    And finally the a quick and easy way to favourite your dashboards in the Android App.

    You can find all the details here: Power BI mobile apps feature summary – November 2016

Power BI – Quick Tip – Overcoming additional columns in CSV or Flat Files

This quick tip explains how to overcome an issue with using CSV or Flat Files and you get additional columns.

I have been working with CSV Files, and what often happens is that you get years added into the columns on a CSV File as shown below is what the original file looks like.

And this is what the source file looks like after we have added some years

Now what happens in the Query Editor is when you import your CSV File it actually hardcodes the number of columns as shown below with our first example which has got 11 columns.

So now what happens is when I update my file to have the additional years and refresh my Preview I see the following.

Solution

Now in order to overcome this, whilst it might not be the most elegant solution it works well and I do the following.

  • I edit the M Code and change it from

    ,Columns= 11

  • To a much higher column
    number like 100 for example
    • ,Columns=100

What this will then do is to to make the total columns for the table be 100

Next I always either transpose or UnPivot the data.

So once I have done that I then have the following in Query Editor

As you can see it has got the additional columns which do go on for some time.

All that I do is to then filter the Value and remove all Blank Values

NOTE: The reason that I do this, is that as new years with data becomes available it will have a value other than blank (possibly a zero at worst case)

Which then leaves me with my CSV File in the format I require with the additional
years, which will be added dynamically?

Conclusion

As you can see by making a simple change to the code, it means when I import future data via CSV or Flat Files that it will still work showing the updated data.

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 – Using Parameters for Flat File / CSV / Excel Sources

I was recently catching up on Blogs as I always find this a wealth of information and it often leads to me learning something new. Which I can then leverage in my current work environment and this is one of those cases.

This is to show how using Power BI and the built-in Parameters it can be capable of handling changing folder locations, as well as fewer errors with Flat Files, CSV’s of Excel  Files. And this does happen fairly often.

I have been working a lot with CSV and Excel sources, and from time to time, when refreshing the data I got the dreaded Formula.Firewall error as shown below.

This then lead me to the blog post by Ken Puls (Power Query Errors: Please Rebuild This Data Combination), where he goes on to show how to work around this. But in his version Ken uses Excel and a table to pass parameters.

Example in order to follow

For this blog post it is best to use a simple example to follow with.

  • In this example I am going to import a CSV File that contains Weather Information from Brisbane
  • I will then create a parameter and link it back to the Source CSV File.
  • Finally I will then show how to create a new query which will reference the Source.

Loading your Source Data

The first thing that we will need to do is load your source data in the typical way.

As with the above example I am loading the CSV File with the Weather information.

Once loaded you will see the following:

Next what I like to do, is to rename this query from BOM-BNE to Source – BOM-BNE.

The reason for this is so that I can differentiate later what is my source file and what is the file that I now want to modify.

Creating the Parameter

In the steps below is where I will now create my parameter and modify the existing Source file to use the parameter.

  • First is to create the Parameter
    • I click on the Manage Parameters Icon in the ribbon and select Manage Parameters
    • Now I create my Parameter as shown below
        • As you can see I have given it a name of “Folder Name – BOM“, this is if I have multiple Folder Parameters I know which one is associated to which Parameter.
        • And then I gave it the location of where my CSV File is.
        • Then I clicked Ok.

Modifying the Source

In this section I am going to modify the Source.

This is to ensure that my source is exactly that, just the source with no other steps.

  • Next is to modify the existing source.
    • I click on my QuerySource – BOM – BNE” and then in the ribbon click on Advanced Editor
    • This now brings up the Advanced Editor, and before I edit anything this is what the code looks like.
    • Now I will replace
      the C:\PBI with the parameter I created earlier “Folder Name – BOM
      • So the existing code has the following:

                  Source = Csv.Document(File.Contents(“C:\PBI\BOM-BNE.csv”)

      • I then modify it with the Parameter:
                 Source = Csv.Document(File.Contents(#”Folder Name – BOM” & “\BOM-BNE.csv”)
        • Now what I have done above is to put in the Parameter Name, which in Power Query requires it to have the following syntax shown in Light Blue
          • #”Parameter Name”
        • Then in order to append this to our existing File Name I had to continue this with adding the & “ shown in Purple
      • Then I clicked Ok.
    • Your data should now refresh the preview and it will basically look the same.

Disabling the Load of the Source

Next is a handy trick that I learnt from Reza Rad (Performance Tip for Power BI; Enable Load Sucks Memory Up) in terms of disabling the Load of tables that are not going to be used in the Power BI Model.

NOTE: What this does is because you do not load the data into your Power BI Model, it does not consume precious memory.

  • So in order to disable the loading I right clicked on the querySource – BOM-BNE” and unticked Enable Load
  • It will prompt you with the following window
    • Click
      Continue
  • So once I completed this it was greyed out.

Referencing your Source

Now in the next step is where I now can easily reference my Source data.

And this is where as per Ken Puls blog, it not only avoids the potential error, but it also separates my source data from my working on the data.

  • To do this you simply right click your query.
    • As with our example I right clickedSource – BOM-BNE” and selected Reference
  • Next I renamed it to “BOM-BNE
  • And then I started working on my Source File to get it into a Shape that I required.
  • Now if you had to look at the Advanced Editor we can see where it references our Source

And what I do generally to make it easier for Navigation in my query Editor is to create groups for my different data aspects as shown below.

And this is a very quick report using the data

You can download a copy of the file here: Using Parameters for Flat File – CSV – Excel Source Data.pbix

BI-NSIGHT – Power BI (PowerPivotPro & Microsoft Helping displaced workers, Visualizing weather from Microsoft OMS) – SSAS (Memory Usage (1200 Compatibility Only) report using Power BI) – SQL Server (SP1, SSRS Updates

Another week close to Christmas and another week of great news in the Business Intelligence world.

Power BI – PowerPivotPro & Microsoft helping displaced workers

A quick mention on how it is great to see both the people at Power Pivot Pro & Microsoft are working together to help people find employment.

The great thing about Power BI, is all that you need is a computer and an internet connection and you are good to go.

You can find the details here: PowerPivotPro and Microsoft help displaced workers with near-free Power BI Training Dec 7th

Power BI – Visualizing weather from Microsoft OMS

This is a great blog post where the people from Microsoft explain how using OMS they can leverage the weather data that they collect and publish it to Power BI.

If you currently use Microsoft OMS this could be a great resource.

You can read up about it here: Visualizing weather information in Power BI with data from Microsoft OMS

SSAS (SQL Server Analysis Services) – New SSAS Memory (1200 Compatibility Only) report using Power BI

Here is another really great resource of information from Kasper De Jonge, where he has updated his SSAS Memory usage report, with the latest versions of SSAS, but also to use Power BI to visualize your memory usage.

Also Kasper has put it on GitHub which means that it is open for anyone to use, as well as to contribute and hopefully make it into an amazing Memory usage report.

You can find the details, as well as how to download the file here: New SSAS memory usage report using Power BI

SQL Server – Service Pack 1

It is not often that there I would blog about a service pack, but this is an exception. As you can see above Microsoft have made available a whole host of features that were previously only for the Enterprise Edition which is now available in not only Standard Edition but also Web, Express & LocalDB.

One thing to note is that it would appear that all of the licensing updates are in the RDBMs space.

There are 3 updates for SSAS whichi is firstly that SSAS is NUMA Aware, I know that this is something people have been asking for, for a long time. As well as Memory Allocation and Heap Fragmentation.

You can find the blog post here, which has all the detailed information, as well as what additional update there are in SP1: SQL Server 2016 Service Pack 1 (SP1) released !!!

SQL Server – SSRS (SQL Server Reporting Services) updates with SP1

Not only where there the licensing features with SQL Server 2016 SP1, but also updates to SSRS, as you can see above the SSRS team is listening to what clients and customers are asking for and have updated the Details view.

They have also updated the Execution logs, so that you can now get some basic information in terms of who is accessing and viewing mobile reports.

And finally direct navigation options for KPI’s, which makes it easier to navigate to a direct link when viewing a KPI.

You can find all the details here: What’s new in SQL Server 2016 SP1 for Reporting Services

BI-NSIGHT – Power BI (Mobile Apps Update, Microsoft Teams, Extending Project Online Content Pack, Featured Data Stories, Streaming Data Set Example from Chris Webb, Dashboard Makeover Webinar) – Azure (Analysis Services)

Another week has gone by, and some more updates in the world of Business Intelligence

Power BI – Mobile Apps Update

There are quite a few updates for the Mobile BI apps, and it is good to see that it is across all platforms.

The easier navigation is always welcome because with a phone it is always tricky to navigate and see what you want to see. It also makes perfect sense that when you open your Mobile app, you want to see your favourites dashboard, because that is what you want to see first.

With regards to notifications, these are great updates, not only is the notification more descriptive, but also easier to dismiss once you have seen the notification.

As well as being able to view your report by location (on windows phone) and the ability to try the SandDance visual on the surface hub.

You can find the mobile apps blog update here: Power BI Mobile Apps feature summary – October 2016

Power BI – Microsoft Teams

Last week or this week Microsoft announced a new platform to enable productivity and collaboration called Microsoft Teams. You can find the details with regards to Microsoft Teams here: Microsoft Teams – Group Chat

And now this week they have now enabled this for Microsoft Power BI. This will further add to the Power BI story in terms of collaboration and I think it will make it even easier for people to communicate effectively and quickly.

You can find more details here: Power BI teams up with Microsoft Teams

Power BI – Extending the Project Online Content Pack

The Office team has been busy creating and extending the Project Online Content Pack. This blog post not only shows you where to get the data, but it also explains how to utilise the data and tips and tricks to get the most out of your data.

If you use Project Online read their blog post here: Extending the PowerBI content pack for Project Online

Power BI – Featured Data Stories

Once again this month from the Power BI Community is the featured data stories.

I always go and have a look, as there are some really good data stories told.

You can find the details here: Congratulations to this month’s Featured Data Stories Gallery submissions

Power BI – Streaming Data Set Example from Chris Webb

This is a really great and insightful post from Chris Webb. Not only does he show and explain how to get the streaming data sets working, but has an example that you can try out with yourself. (I personally did try it and it works!)

For me there are two things to take away from this. The first is that it is really quite easy and simple to create a streaming data set. Second is that I am already thinking of how to leverage this for other scenario’s.

You can find Chris’s Blog post here: Pushing Data From Excel To Power BI Using Streaming Datasets

Power BI – Dashboard Makeover Webinar & Submit your data

There is a Webinar on 17 November 2016, where you can see how they do a dashboard makeover.

Not only that but there also is the option for you to submit your entry for it to be made over.

You can find the details here: Submit your work and attend our next Dashboard Makeover

Azure – Analysis Services

In the link below is a great blog post by Kasper De Jonge and he goes into some great high level details as to why you would want to use Analysis Services in Azure. It is a really great article and does give some food for thought.

An example would be never having to upgrade to another version of Analysis Services, as well as having it being Highly Available, which in the past takes a significant amount of effort.

You can find all the details here: Analysis Services in Azure, when and why…

Power BI – Quick Tips why Q&A (Natural Query Language) is Awesome

Q&A (Natural Query Language) is by far one of the features that I find really gives the WOW factor when showing people the capabilities of Power BI. And in my opinion
it is awesome.

So I thought I would give a quick blog post in terms of how best you can leverage this, and hopefully also enable you to be able to WOW people.

The aim of this blog post is to show you how to quickly see and understand how use the WOW features in Q&A

Asking Q&A Questions relating to dates or times

The one thing that I think a lot of people do not realize that Q&A is aware of dates and times.

And by that I mean that Q&A knows when you mean today, it is today’s current date.

It’s always easiest to show with an example.

As you can see above I have asked the question what is my total revenue for today. (Where today’s date is 08 November 2016)

The thing here to take note of is that I do not have anything in my model that defines what “Today” is. Q&A is smart enough to know that today’s date is 08 November 2016.

But the WOW factor is that when you pin the above Q&A question to your dashboard, it will always show the revenue for your current day or date. (NOTE: As long as you are scheduling a refresh of your data)

So what this means is that this particular dashboard tile will always show “Today’s” data without having to go and do any actual filtering or altering of any existing reports. I personally think that for people on the go, they just want to have the ability to have a quick glance at how things are doing without having to interact or filter their dashboard tiles. And this gives them that capability!

And to make it even better, you could then add an Alert, so that when the value is too high or too low, you will automatically get an alert. This makes your dashboard give your dashboard better insights, but also makes it proactive, instead of being reactive.

Other related date or times that you can use

The other dates that I have used in the past are the following:

  • Today
  • Yesterday
  • This Week, Last Week
  • This Month, Last Month
  • This Year, Last Year

As well as time related features

  • Right now
  • Past Minute, Past (x) Minutes
  • Past Hour, Past (x) Hours

Modify your Q&A Question if it is not answering as expected

The other thing that I have found is that whilst Q&A is very good at getting it right, sometimes due to your data, it can be difficult, or not answer the question in the way that you  require.

But the Microsoft Power BI Team has done some great work, and if you are not aware when asking a Q&A question, you do have the ability to actually change what it is inputting into the fields in the Visualizations.

Once again best to show with an example.

I was asking the question as shown below.

Now what I wanted to do, was to rather have the Industry as the Legend and Product on the Axis, and have it as a Treemap visual. (Yes I am aware that I might have been able to change the order around, but it is to illustrate the example)

Now if you look to the right hand side of the screen you will see the Visualizations, Filters & Fields, click on the arrows to expand the fields.

And once done that you now will see the following below.

And now you can manually change your fields, as well as your visualizations to how you want it to look.

As with my example I swopped around the Industry and Product, and then changed the Visualization to a Treemap

So once done my Q&A question now looked like the following below.

I then took this and pinned it to my dashboard.

As you can see in doing it this way, it makes it very easy to get the desired result.

Conclusion

As you can see there are multiple ways to use Q&A as well as to create that WOW factor, and really get people excited about using Power BI.

Finally if you want to view the Q&A tips blog post, please see the link below. It is well worth the read.

Tips for asking questions in Power BI Q&A