BI-NSIGHT – Power BI (Mobile App Update – Android Tablets, Navigation Preview, AMA, Driving Power BI Adoption in your Organisation) – Excel (Get & Transform Update)

It does appear that as we approach the festive season, the Microsoft team are still full steam ahead, which is great for myself and the Data Analytics community.

Power BI – Mobile App update for Android Tablets

As per their blog post, it is great to see that there now is an App for the Android tablets, whilst this has been around for quite some time on the iPad tables, it has now come to Android.

It is great to see and you can find the blog post here: Now in preview: the Power BI you love on your Android tablet

Power BI – Navigation Preview

I have to say that I have already enabled the preview features, and whilst it did take a little time to get used to, it is definitely a step in the right direction.

It makes it easier to navigate when you have a lot of reports, dashboards and workspaces. As well as less clicks to get to the options that you want to use.

You can find more details here: Announcing the Power BI Navigation Preview

Power BI – AMA (Ask Microsoft Anything)

Depending on when you read this, you might be a little late, but it is great concept where you can ask Microsoft anything relating to Power BI.

I have no doubt that there will be some tough questions posed, but also a great wealth of information into Power BI.

You can see the dates and times above.

You can find the blog post here: Announcing the Power BI Ask Microsoft Anything

Power BI – Driving Power BI Adoption in your Organisation

This is a great blog post in which Microsoft gives an example of how they have used and enabled the business into data driven decisions using Power BI and how to get it adopted within Microsoft.

The really interesting part I think is that Microsoft is a large multi-country company, so if they can adopt Power BI in such a large scale, then it should be achievable with any other companies.

And not only that, but because Power BI is their own product I often like to think that because they build it, they might not use it as much as an external organisation, but they are which is great to see.

I would recommend reading through this if you are looking to get your organisation into Power BI: Driving Power BI adoption in your organization – Learn how Microsoft does this at scale

Excel – Get & Transform Update

It is great to see that there have been so many ypdates to the Get & Transform for Excel. I often feel like the Excel people do get left behind due to the sheer pace from the Power BI Team.

All of the updates are shown below

  • Web Connector—UX support for specifying HTTP Request Headers.
  • OData Connector—support for “Select Related Tables” option.
  • Oracle Connector—improved Navigator preview performance.
  • SAP HANA Connector—enhancements to parameter input UX.
  • Query Dependencies view from Query Editor.
  • Query Editor ribbon support for scalar values.
  • Add custom column based on function invocation.
  • Expand & Aggregate columns provide support for “Load More” values.
  • Convert table column to a list—new transformation.
  • Select as you type in drop-down menus.

You can read their blog post to get more details: November 2016 updates for Get & Transform in Excel 2016 and the Power Query add-in

Power BI – Using Parameters in Calculated Measures (Making Calculated Measures Dynamic based on Parameter)

What this is going to explain below is how I use a parameter that a user has entered to make a Dynamic Calculated Measure based on the Parameter entered.

Example Scenario

  • In my model I have got Parameters for the Fiscal Start Year
  • So I want to create a calculated Measure that will get me the values for 4th Year going forward
    • So what we are looking to calculate is the following, which is the Value for 2015-16
  • And then if we change the Parameters for the Fiscal Start Year as shown below
  • So I want to create a Calculated Measure that will get me the values for 4th Year going forward
    • So what we are looking to calculate is the following, which is the Value for 2017-18
  • But using the same calculation

Getting the Parameter into a table

In the steps below I am going to explain how to get our Parameter Value for the Fiscal Start Month into a table in the Query Editor

  • Go into the Query Editor
  • Then click on New Source, and select Blank Query
  • Now in my Example I name the Query “Parameter – Fiscal Start Year”
  • Next I clicked on Advanced Editor
  • I then put in the following M Code

    let

    Source = #table({“Fiscal Start Year Number”},{{#”Fiscal Start Year”}})

    in

    Source

    • As you can see above I created a table with the Column Name “Fiscal Start Year Number” highlighted in PURPLE
    • And then defined the column to have the Parameter called #”Fiscal Start Year”
      highlighted in LIGHT BLUE
  • This then results in the following table as shown below.
  • What I then did was to use the standard Query Editor Functions and changed it to the Data Type of Whole Number
  • NOTE: This is so that I can then use it to go backwards and forwards for my dynamic calculated measure.
  • The final step was to load it into my Power BI Desktop Model

Creating the Dynamic Calculated Measure

Next I will explain how I created my dynamic calculated measure, which will change based on what the Fiscal Start Date Parameter that has been selected.

  • Below is my Dynamic Calculated Measure syntax with the explanation what I did afterwards.

    Total Value for 3rd Year =

    CALCULATE (

    [Total Value],

    FILTER (

    ‘Budget Data without New Year’,

    ‘Budget Data without New Year'[Fiscal Year Number]

    = VALUES ( ‘Parameter – Fiscal Start Year'[Fiscal Start Year Number] ) + 3

    )

    )

    • What I have done is to name and start my Calculated Measure and put in the filter.
    • The part where the magic happens in the Filter
      • In here is where I specify that I want to look at the ‘Budget Data without New Year'[Fiscal Year Number] and compare that with the VALUES from my parameter ‘Parameter – Fiscal Start Year'[Fiscal Start Year Number]
      • And then I always want to go forward by 3 to get the 4th year going forward
  • So once this has run I should get the following as shown previously for Fiscal Start Year Parameter 2012
    • And I get the following below.
  • So if I change the Fiscal Start Year Parameter to 2014 once this has run I should get the following as shown previously.
    • And I get the following below.

Conclusion

By using Parameters and putting it into a table means that this can then be used to dynamically create other calculated measures.

You can download the PBIX file with the example data here: UnPivot Other Columns.pbix

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