Adatis BI Blogs

Coming Soon in Power BI - June 2017 & Beyond

Whilst I wasn’t fortunate enough to attend the Microsoft Data Summit on June 12-13 2017, I managed to view the keynote through a live webinar.  The majority of this session contained details of what is coming soon in Power BI, more than likely over the next few months.  We all know the ‘roadmap’ can be as long as a piece of string, but I am hopeful the features mentioned in this blog will all be available by the end of the calendar year. Without further ado, I will move onto the features. Out Now The below features were actually released in the June Power BI Desktop Download. ü  Data bars for new table & matrix (preview) o   This is the concept of having spark lines within a table or matrix, providing trend analysis in a visual way.  ü  Fonts in Visuals o   You can now alter the font in a visual, the same as the functionality within a text box property.  These small configurations actually make a big difference. Coming Soon   ü  New SKUs for power bi premium specifically targeting embedded workloads...  EM1 and EM2 SKUs starting at $625 per month.  The difference pricing tiers are:   ü  Embed using new SKU’s into SharePoint and MS Teams easily o   I wander if the code will be completely free? ü  New Visio diagram control – auto mapping to data model entities.  Copy and paste a Visio diagram into power BI and it hooks up the data elements. o   This looked really slick in the Microsoft demo but previous experiences with this functionality un SharePoint was a little clunky and fiddly. ü  Embed a Power APP into Power BI – with write back functionality  o   This could replace MDS for certain scenarios, depending on the how complex your reference/master data is.    ü  New Quick Measure gallery – a new DAX measure, upload it to the gallery and have your name referenced in the Product. o   Chris Webb recently had this privilege, with a link to his blog series here. ü  Better Custom Visual support. o   New button to install custom visuals from store without having to download and import first. ü  Ability to remove stop words in the Word Cloud Custom Visual ü  Drill through to other report tabs using Page Settings. o   This is huge for Power BI. Something seemingly so simple is not available but I for one am very excited for when this feature is released. ü  Bookmark pane to facilitate a ‘save state’ (all current filter settings).  In addition, it will support analysts who want to tell a story or provide a walkthrough. o   Show/hide visuals on a page when building story ü  Create buttons o   Turn a button into a report page link o   Use buttons to show/hide visuals o   Again, a simple, but MASSIVE feature for Power BI.  This really helps with the user’s reporting journey. ü  Quick insights type functionality built into visuals and desktop to answer “why” questions -  using AI ü  Waterfall chart improvements ü  Cortana Improvements o   More integration o   Using colleague names in Cortana to find PBI workbooks o   Conversational Q&A – refine answer with further questions ü  What-If Analysis o   Like Excel ü  Annotation support for presenting back to customers.  o   In effect, you can doodle or draw on top of a Power BI report. Further Reading I would advise subscribing to the Power BI Blog, in which you will hear of any new announcements to features, etc.  In addition, there are some other great pages out there too: o   Power BI Blog - o   Power BI Premium Capacity White Paper -   o   Chris Webb Blog Page – Contact Me If you have any questions or thoughts, please leave a comment below.  My Twitter details are also provided. Twitter:                @CallumGAdatis

Handling Web Page Errors in Power BI Query Editor

I was recently asked if it was possible to handle Error Rows in the Query Editor and more specifically, to web pages that do not exist from source. The user broached me with this question after following a previous blog on looping through multiple web pages in Query Editor – found here. In my blog post, there were no errors from the loop output but in the user’s workbook, they were losing data directly below the expanded error row. My first suggestion was to use the Remove Errors UI option, which would delete the problematic row. However, they wanted a way of treating the errors as an Unknown Member and did not want to lose the data entirely It is assumed consumers of this blog already have knowledge of the Power BI Query Editor and the UI functions used in the examples.   Scenario I created a new workbook that connects to local Council Facebook pages. Each page has a unique Object ID, which will be used as the parameter in the loop. The Council “Camberley” deliberately contains an invalid Object ID. I then proceeded to create a Parameter and Function, replicating the exact steps from my previous blog. When I invoke the function (through the use of a Custom Column), the following is produced: As you can see, Camberley Council produces an error in the fnInvokeCouncils column. If we expand the contents (highlighted in yellow), the Facebook page data appears. Upon further inspection, the Farnham and Rushmoor council data are available, but Camberley (incorrect Object ID) and Guildford are not. The error message is a little misleading but let’s save the debugging debate for another day. The key observation is “Guildford” data is not available, simply because it comes after “Camberley” in the list. Whilst we want to see errors in a Query, we do not want them causing data loss. Resolution As I mentioned at the beginning of this article, using the Remove Errors function would prevent the loss of Guildford data. However, the user needs to handle errors as Unknown Members and conform to a typical Kimball Data Warehouse.  I am sure there are many ways to fulfil the requirement, but here is how I approached it: 1.       Duplicate the existing ‘Councils’ query, naming it ‘Councils Error Rows’. 2.       Switch back to the ‘Councils’ query and Remove Errors, leaving only three records:   3.       Expand the fnInvokeCouncils column, opening up the underlying fields and data: 4.       In the ‘Council Error Rows’ query, apply the Replace Errors UI function - inserting the string “Validation Failed”. 5.       Add a Custom Column, writing the following M: if [fnInvokeCouncils] = "Validation Failed" then 1 else 0 This is a simple IF statement that sets the error rows to 1. 6.       Now filter the selection to only display ErrorRows with the value of 1. This is achieved by using the Filter Rows UI function. The ‘Council Error Rows’ query now looks like the following: 7.       The columns must match the ‘Councils’ query, meaning 4 new Custom Columns are needed. We can hardcode the values and remove any unwanted columns.     8.       Right click on the previously modified ‘Councils’ query select Reference. Name the query ‘All Councils’. This makes it easier to track the transformations and persists any future changes made to the raw data.   9.       Within the ‘All Council’ query, select Append Query transformation. Choose ‘Council Error Rows’ as the table to append and click OK.   10.   We are now left with a Union of both datasets, containing the Unknown Member and data from all other Councils. 11.   The Unknown Member record is visible within the final Query.     Summary I have shown you how to get around two different problems with Error Rows in the Power BI Query Editor. The first is how to retain all correct data, whilst the second is keeping error records and inserting them back into the dataset as an Unknown Member. Both methods are dynamic, meaning that if we added a new Council called ‘Basingstoke’, we would see the underlying data regardless of whether the underlying Facebook Object ID exists or not. Whilst none of the transformations in this blog are overly technical, there are quite a few hoops to jump through to retain all data from a web page/Facebook. Having said that, I am sure there are a few other ways people could approach this problem. I would be really interested to speak to anyone who does have an alternative solution. Further Reading Query Editor Basics (Power BI blog) – Unknown Members in Data Warehousing - Loop through Multiple Web Pages using Power Query - Contact Me If you would like a copy of the workbook containing the examples or want to know more about the Query Editor within Power BI, please leave a comment below. My Twitter details are also provided. Twitter:  @CallumGAdatis

Slicer Properties in Power BI: Header or Title?

I recently shared a Power BI Report with a customer and they reported that the “Clear Selections” option (Eraser icon) was not available when they used the Slicer.  It took me a while to work out why this was. This blog will illustrate how you can lose the “Clear Selections” functionality, depending on what Format settings are applied to a Slicer.  I will also show how to work around the formatting constraints, which will help prevent you from ever experiencing the issue. In order to follow the examples, you will need access to Adventure Works 2014 SSAS Tabular and of course, Power BI Desktop.  Follow the appropriate links to download what you need. Use Case I have created a very simple report (available on request), using a Product Category Slicer and a Map to display Internet Total Units.  o   Format property. o   Select All is off, ensuring multi select is allowed. o   Header is off. o   Title is on, used instead of Header and configured to look like the below: After multi-selecting the attributes in the Slicer, I tried to “Clear Selections” – which is normally available as an option like below: However, it does not appear in the report I created: The reason I cannot see the “Clear Selections” option is because I am not using a Header.  When I use this instead of Title, the Slicer contains the required feature. Header vs Title This made me wonder what other differences are there between Title and Header but in fact, there aren’t many.  The subtle differences to be aware are displayed and described below: o   Header can have an Outline, which includes the ability to underline text. o   Header is constrained to displaying the name of the attribute (“Product Category Name”), whereas a Title can be customised (“Select Category”).  You can rename your source data attribute to get around this, however. o   Title enables you align the text, but this is not possible with a Header. o   Header contains the “Clear Selection” option. Workarounds There are couple of ways to work around the missing “Clear Selections” issue, which I will demonstrate below. Option 1 I could simply switch from a Title to a Header, but then we would lose the ability to centre align the description.  Instead, we can set both options to ‘On’.   After some formatting, the Slicer is pictured below. o   The Header text is set to White, therefore, it not visible.  o   You can still see the “Clear Selections” option. o   However, there is white space in between the Title and Header.  This not only wasted space, but also looks a bit strange from a visual perspective. o   The actual sections themselves (e.g Bike) are a bit squashed and disproportionate to the Slicer border.   Option 2 The other workaround involves a little more work, but gives the impression that only a Title is being used, but with the added functionality of the “Clear Selections” option.  Furthermore, there is no longer the white, empty space.     o   Two objects were used to produce the result: ·         Text Box ·         Slicer o   The text box has been formatted and labelled with ‘Select Category’, using the Title property.  This allows you to change the font colour. o   The Slicer is using the Header option, ensuring “Clear Selections” is available. Title is turned off to reduce the empty space.   Summary So why are there two types of properties for Slicers and other objects like Text Boxes?  I am not entirely sure myself, but occasionally, you may want a Slicer to contain a Title with an Attribute Name as the Header underneath. This gives you a Title > Sub-Title concept (illustrated below).  I do understand why the “Clear Selections” feature is specific to the Header setting, as it directly relates to the Slicer attribute.   There are certainly ways of solving this issue - here are just a couple of suggestions: 1.       Providing continuity across both the Header and Title format settings.  Quick fix, but not necessarily solving the ambiguity around both options. 2.       The Header setting is contained within the Title, meaning both sets of functionality are merged into one.  It would make the usability of a Slicer (especially from a development perspective) a lot better. Whatever Microsoft decide to do in the future, I really hope they tidy up and fully define the Settings within Visuals, Slicer and Text Boxes.  Some things are confusing, especially to self-serve Business Analysts who rely on intuitive reporting tools. Further Reading Power BI Documentation – Power BI Community Blog -   Contact Me If you would like a copy of the workbook containing the examples or want to know more about any of the Power BI settings/properties not mentioned, please leave a comment below.  My Twitter details are also provided below.Twitter:                                @CallumGAdatis