Adatis BI Blogs

Embed PowerApps into Power BI Dashboards – Part 1

Having read Matt How’s blog (found here) about PowerApps, not only did it get me interested in the technology, I also wondered how well (if at all possible) it could integrate with Power BI. Our friend Google soon told me that it was already possible to embed PowerApps into Power BI, released in the April update. However, apart from this blog by Ankit Saraf, there aren’t many professionals sharing their experiences. In addition, leveraging Direct Query mode in Power BI means we can simulate real time user input and reporting. To replicate my solution below, you will need an understanding of PowerApps, Azure SQL Database, Flow and the Common Data Service (CDS). The Further Reading section provides some good links to get you up to speed. I have broken the blog into 2 parts: -          Part 1: How Power BI visuals and PowerApps can be used together. -          Part 2: Benefits and Drawbacks of the tools/processes used. Solution I picked a typical use case that would link Power BI and PowerApps – Actual vs. Target. The Power App will be used for adjusting target values, whilst an Azure SQL Database will contain the original target and actual values. All data and Power App interaction will be embedded into a Power BI Dashboard. Create Sample Tables and Data in Azure SQL Database Create and populate two tables – dbo.SvT for static actual vs. target data and dbo.SvTAdjusted that will eventually contain the adjusted target data from the PowerApps form.             Note:     Azure SQL tables require a Primary Key column to communicate with Flow and consume CDS data. Create PowerApp Create an Environment within the PowerApps service, adding two new Connections:   1.       Connection to the CDS, using my company Microsoft account. This is where the adjusted budget values reside. 2.       Connection to the Azure SQL database, which will become the destination table to store the CDS Power App data.   The next step is to import the SQL Data from dbo.SvTAdjusted directly into a CDS PowerApp.     This automatically creates a user form containing the data. Here is where you can customise the PowerApp, such as making fields read only and configuring look and feel.     Publish the App and test and change the ‘Target’ values to test. Create Flow trigger Navigate to and login. Create a new flow, searching for ‘Common Data Service’ as the connector. Select the below and create the Flow.     Select the PowerApp CDS Entity (Adjusted Target) as source.     Add a new step (Add an Action) and search for ‘SQL Server’. Select SQL Server – Update Row as the destination and map to the dbo.SvTAdjusted table. The column data types between CDS and Azure SQL Database must match when being mapped. Save the Flow.       Create Power BI Report Create a Power BI Desktop report and connect to the Azure SQL Database. Set up the one to one relationship on ‘PrincipalID’, between the tables. Create some KPI’s and a table to compare dbo.SvT and dbo.SvTAdjusted metrics. In the below example, the ‘Adjusted Budget’ metric will change when we make changes in the CDS Power App. Embed Power App into Dashboard Publish Power BI Desktop report and pin as a live page. To embed the PowerApp into the Dashboard, add a Tile and select Web Content. The App ID can be found under Apps in the Power Apps web portal. Simply paste the App ID into [AppID].  <iframe width="98%" height="98%" src=",34,55,1)&appId=/providers/Microsoft.PowerApps/apps/AppID]   The PowerApp is added as a Dashboard tile. It is now possible to change the ‘Budget’ values.       Time to test everything works. Change the values for all three records and refresh the Power BI Dashboard. The values have changed almost instantly!     Further Reading Check out Part 2 of the blog, where I will be discussing the benefits and drawbacks I have found with using Power BI and PowerApps together. Find other recommended resources below. o   Matt How’s Blog - o   Embed PowerApps into Power BI - o   PowerApps - o   Flow - o   Common Data Service - Contact Me If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request.Twitter:                @DataVizWhizz

Embed PowerApps into Power BI Dashboards – Part 2

Part 2 of this blog focuses on my experiences with PowerApps, Flow and Power BI. Part 1 was more of a demo and ‘How to’ guide, but when I read an article online, I always find known limitations, challenges or workarounds as the most interesting takeaways. Without further ado, here are my findings.   A summary of both blogs below: -          Part 1: How Power BI visuals and PowerApps can be used together. -          Part 2: Benefits and Drawbacks of the tools/processes used. Benefits -          Easy to get started. Rolling out Power Apps, Flow and Azure databases into production of course needs careful thought, but for Proof of Concept’s, Flow (2,000 runs per month) and PowerApps (for Office 365 users) are free to use. Links to the price breakdowns are provided in the Further Reading section below. -          There are a range of Wizards, Templates and GUI’s. All the tools used offer great templates for moving or inputting data and the fact barely any code is needed, makes it simple for business users. Following a couple of YouTube tutorials on each technology will get people up to speed very quickly. -          Azure technologies provide seamless integration between Microsoft tools. Whilst there are some other well-known, reputable cloud service providers around, using one product is always going to produce a slicker solution. Having less configuration steps means less chance of human error. -          Customisable features of PowerApps give the ability to mask, validate and format the PowerApp screens. It also makes the user entry a more pleasant experience, as the forms are more intuitive. Limitations -          You can only embed PowerApps into a Dashboard – as a Tile. I am not sure if moving PowerApps into a Power BI Report is on the roadmap, but I would be surprised if it was never supported. -          Power BI Dashboards are cached and not entirely real time. You can change the cache settings to 15 minutes, but the best way to ensure your visuals contain the latest Power App data is to manually refresh your page in the browser. Reports do update automatically, which makes it even more frustrating. -          Common Data Service (CDS) is a preview Data Connector in Power BI. As a result, you need to either have your environment set as ‘America’ and/or been given the beta by Microsoft. If I had access to this connector, there would have been no need to have the Azure SQL Database or Flow trigger. Milinda Vitharana’s blog shows how to enable CDS Power BI Integration. -          If you wanted to use an on-premise database instead of an Azure database, an additional step is needed. A Data Gateway (link here) must be installed to move the Power App data back into the SQL database. Therefore, I would always recommend (where possible) using PaaS or other cloud services, as they talk to each other natively. -          The error handling within the PowerApps is still quite limited. If Flow fails when updating data between PowerApps and Azure SQL Database, nothing is captured within the form itself. An Admin would need to check the Flow job or set up email alerts for user’s peace of mind.     Conclusion The initial signs look promising for Power BI and PowerApps integration. I managed to create an Actual vs Target Proof of Concept in just a matter of hours, without any real coding. There are still quite a few drawbacks and hoops to jump through to bring everything into a Power BI Dashboard, but I can only see things getting easier from this point. There are other use cases for embedding a PowerApp into Power BI, such as monitoring live sales and re-ordering stock within a PowerApp or updating product descriptions that automatically updates the Dashboard attributes. Giving someone the ability to directly interact with a Dashboard and make instant business decisions is priceless in today’s fast paced world. Further Reading Find other recommended resources below. o   PowerApps Pricing - o   Flow Pricing - o   Milinda Vitharana’s blog - Contact Me If you have any questions or want to share your experiences with PowerApps and Power BI, feel free to leave a comment below. All scripts and workbooks are available upon request. Twitter:            @DataVizWhizz

Loop Through Webpages with Multiple Parameters in Power Query

Back in February 2016, I posted a step by step article (found here) on how to loop through a website and dynamically pull down multiple pages into a single table in Power Query. I was recently approached by a gentleman who had read my blog, wanting to enhance the looping process. The requirements were: Current Problem: -          The user can currently only loop through Year, having to hard code the Page and Month. Requirements: -          Dynamically loop through three webpage parameters and output them into a single table – highlighted below: -          Specify the Page, Year and Month to loop through o   Years 2010 - 2017 o   Months 1 - 12 o   Pages 1 - 10 Parameters Whilst GUI Parameters are not a new concept in Power Query, they did not exist when I wrote my initial blog.  As a result, I had to code a parameter into the M query and then define it as a function.  A similar concept will be adopted here, but the out of the box parameter functionality makes life so much easier now. I previously wrote an article explaining how to set up parameters (here), so I will simply show you the output of them below: Note – all three parameters are set to Text.  This is needed in order to pass the parameters into the website address later on. Web Connection The first step is to import the data from the website (shown above) using the web connector.  To pass our parameter values into the URL, click the cog in the first Applied Step – typically called ‘Source’. This will open a new window, where by design, the hardcoded URL is shown under the Basic radio button.  Select Advanced and you will see additional configuration capabilities. URL Parts enable you to build up elements of the webpage, whilst dynamically passing in parameters.  The URL Preview will show you how the URL looks with any parameters or URL Parts.  All other options can be ignored for this example. The URL Parts had to be broken down quite significantly and I will show you both the code and how it looks in the Advanced window. User Interface: The sections highlighted in red are the parameters and sit in between some of the hard-coded URL text Code Breakdown: -          Text = -          Parameter = [Page] -          Text = &view=calendargross&yr= -          Parameter = [Year] -          Text = &month= -          Parameter = [Month] -          Text = &p=.htm URL Preview:{Page}&view=calendargross&yr={Year}&month={Month}&p=.htm Year/Month/Page Permutations Table Before creating/invoking the function, a table that contains the different years, months and pages is needed.  There are many ways to achieve this, but I used a cross join technique (found here) with three disparate tables.  I followed the end user requirements and end up with a table like below: This will be the final table where the function is called. Function Let’s go back to the original table, where the parameters have been applied to the Web URL.  Right click the table and select Create Function.  Nothing else is required (apart from naming it), as Power Query will automatically package up the table and you end up with a new Function like below: The last step is to invoke the function.  Go back to the Permutations table (called ‘All Data’ in my example), and go to Add Column > Invoke Custom Function. When prompted with the next screen, map each parameter to the equivalent column.  The red highlighted values are the columns and yellow contain the parameters.  Both are named the same in my example and make mapping even easier. The function will loop through each URL permutation, dynamically changing the parameters on execution.  Once it has finished, tidy up any errors (for Years, Pages and/or Months) that do not exist.  Apply all transformations within the Query Editor and that is it – the data is ready for Reporting!  You may need to configure some of the measures (e.g. Total Gross) to be numbers, but that is easy enough to do within the Query Editor. The example in this blog can be adapted in many ways and illustrates how dynamic Power Query can be – both in Excel and Power BI. Further Reading o   Cross Join in Power BI – o   Reza Rad Custom Functions – Contact Me If you would like a copy of the PBIX workbook or have any general questions, feel free to leave a comment below. Twitter:                @DataVizWhizz

Themes in Power BI – Part 2

In September’s Power BI Desktop release, one of Microsoft’s announcements was a new offering of Themes.  My previous blog back in March 2017 explained what a Theme could (or couldn’t!) do, but I am pleased to say Microsoft listened to the community and have improved it greatly. Originally, we were limited to setting a few page or chart colours - mainly tables and matrices.  Appreciated this was a first release from Microsoft, but it’s safe to say I was left uninspired.  The good news is Themes can now do a lot more, including: -          Set the font size for specific charts e.g. 14 -          Set visuals to use a specific font type style e.g. Calibri -          Turn off/on chart data labels -          Set default property values for a chart -          “*” functionality that will apply a specific property across all charts This blog will focus on how to not only use the new features, but how they can be brought together to form a larger, organizational configuration file. If you would like to follow my examples, please ensure you have downloaded the latest version of Power BI Desktop – found here. New JSON In order to extend the Adatis Theme, a new section called visualStyles must be added to the JSON.  The format is as follows: The highlighted JSON sections give you the ability to use ‘*’ syntax, which means the Theme format will apply to everything underneath it.  This is useful for things if you want to apply a global font size across the entire report, rather than needing to configure every chart/visual. Now, let’s pick a simple use case.  Client A requires the following to always be applied to a Pie Chart in Power BI Desktop: -          Switch on the Pie Chart Legend -          Set Text Size to 14 -          Set Font Family to Calibri Light. Default Pie Chart: -          By design, Legends are turned off. The configuration file is tweaked to contain the new JSON - highlighted in yellow. {   "name": "Adatis",   "dataColors": [ "#002C5C", "#006FBA", "#81C341", "#F4AA00", "#003876", "#00448F", "#0051A9", "#007ED4", "#008DED", "#74B238", "#8EC954", "#DB9800", "#FFB60F" ],   "background": "#FFFFFF",   "foreground": "#002C5C",   "tableAccent": "#81C341",   "visualStyles": {     "pieChart": {       "*": {         "legend": [           {             "show": true,             "fontSize": 14,             "fontFamily": "Calibri Light"           }         ]       }     }   } } The final step is to import the modified JSON file, resulting in a completely different looking Pie Chart.  Applied Theme: 1.       The traditional Adatis chart colours have been applied. 2.       A Legend is visible – in both the properties pane and the visual itself. 3.       Font family and Text Size are updated. Adatis Theme Extended I will now add some additional configurations to the Adatis Themes file. -          Turn On all Chart Legends. -          Pie Chart Label Style set to Percent of Total. -          Set Card Label and Category Label Font Size to 12, but all other visuals use 10. This changes the JSON code significantly, but by following the visualStyles format, this was more straightforward than it seems. {   "name": "Adatis",   "dataColors": [ "#002C5C", "#006FBA", "#81C341", "#F4AA00", "#003876", "#00448F", "#0051A9", "#007ED4", "#008DED", "#74B238", "#8EC954", "#DB9800", "#FFB60F" ],   "background": "#FFFFFF",   "foreground": "#002C5C",   "tableAccent": "#81C341",   "visualStyles": {     "*": {       "*": {         "*": [           {             "fontSize": 10,             "fontFamily":  "Calibri"           }         ],          "legend":[{                     "show":true                 }]       }     },     "card": {       "*": {         "labels": [           {             "fontSize": 12           }         ],         "categoryLabels": [           {             "fontSize": 12           }         ]       }     },         "pieChart": {           "*": {             "labels": [               {                 "labelStyle": "Percent of total"               }             ]           }      }   } } The report is significantly different.  I will pinpoint a specific area of the report to illustrate where the configurations occurred. Before: After: 1.       Global font size is set to 10. 2.       Percent of Total setting applied to Pie Charts. 3.       Category Labels and Labels set to font size 12 for all Cards. What else can Themes do? This article is only scratching the surface at what Power BI Themes can now do.  Click here to find the extensive list of properties that can be configured.  It is worth mentioning that if you have limited JSON experience, some of the configurables can be a little tricky to set up.  However, there are quite a few blogs out there that can get anyone up to speed, which I will further provide below.  Limitations There are a couple of limitations, as well some things I have experienced: -          Debugging JSON (natively within Power BI) is not supported and a syntax error occurs. Power BI will give you a generic error message like below: -          Data configurations are not supported ·         This is understandable, though.  Every report tab contains different slices or filtering of data, but colours, fonts and chart properties tend to remain the same. -          Setting Card font and background colours does not appear to be possible yet.  Or at least I have been unable to get it to work or find any supporting documentation from Microsoft to suggest it is. -          Overriding a Theme ·         Individually changing a chart property will override the Theme JSON file.  I was caught out by this and couldn’t understand why the configuration wouldn’t change on a Pie Chart. ·         To get around this, use Revert to default within a chart property and it will re-apply the Theme.  This option exists in each top-level chart property – see example below.   Further Reading o   Jason Lengstorf’s JSON Blog – o   Power BI Desktop September Update - o   Power BI Report Themes BI Blog - o   Themes Gallery (containing example JSON files) - Contact Me If you would like a copy of the PBIX workbook/JSON Theme file or have any general questions, feel free to leave a comment below.  I am particularly interested to see what Theme files other people have created and if there any other known limitations.    Twitter:                @DataVizWhizz

Direct Query vs Live Connection in Power BI – Part 2

This instalment of the Power BI blog series focuses on the two other differences between Direct Query and Live Connection in Power BI.  If you haven’t done so already, be sure to check out Part 1, which concentrated on Quick Measures and Relationships. Feature Differences Find below a Power BI Direct Query screen, focusing on the final 2 differences (highlighted green): o   New Hierarchy o   Change to Import Mode New Hierarchy Hierarchies are very useful with Power BI, especially when wanting to drill up and down within levels of data e.g. Day > Month > Year. Let’s try and create a New Hierarchy in Live Connection mode: New Hierarchy does not appear in the list of options. This is because hierarchies are typically created within the OLAP Cube, therefore, it makes sense why the option is not available.  However, I would argue that Direct Query allows you create a hierarchy, so why can’t Live Connection? Direct Query stores the hierarchies within a smaller Power BI model (Tabular Cube) running on a report developers local machine.  Ironically, Live Connection does exactly the same thing when you create an ad hoc measure in Power BI. It is surely a matter of time before hierarchies are also supported. Change to Import Mode Direct Query mode supports the ability to easily switch to Import Mode.  This is a useful option, especially for a self-serve analyst wanting to make transformations and shape the data.  By right clicking the highlighted option below, a simple wizard appears: When the import completes, the data (from tables in the database) will be stored in a Tabular cube on the local machine where Power BI Desktop is running.  If we navigate to the same area within Live Connection, notice there is no option to change to Import Mode (“click to change”: Once again, I am not really sure why this feature isn’t support in Live Connection mode.  I can understand why it would be more difficult to convert a Multidimensional Cube (MDX) into a local Tabular Cube (DAX), but if Live Connection already points to Tabular, it’s an exact copy.  To vote for Import Mode functionality within Power BI Live connection, click here. Coming Soon Part 3 is the final instalment of the blog series, specifically focusing on the underlying Power BI Data Models in Direct Query and Live Connection. Further Reading Other than the Power BI Blog, there are some other great pages out there too: o   Power BI Blog - o   Import Mode vs. Direct Query - o   Direct Query in Power BI - o   Live Connection in Power BI –   Contact Me If you have any questions or thoughts, please leave a comment below.  My Twitter details are also provided. Twitter:                @DataVizWhizz

Direct Query vs. Live Connection in Power BI – Part 1

There are lots articles out there that compare Import Mode vs.Direct Query, but people rarely talk about if there are any differences between Direct Query and Live Connection.  “Wait. Aren’t they the same thing?”  Well, not quite. The first big difference between Direct Query and Live Connection is the type of data source used for the connection.  The former uses a database connection (typically SQL Server), whilst the latter requires an Analysis Services OLAP Cube. This blog series won’t explain what Direct Query and Live Connection can do (found here and here), but will instead highlight the other subtle differences between the two connections.  Feature Differences There aren’t any features I can find that are available in Live Connection and not Direct Query.  However, there are a few the other way around. I will first show you a Power BI Direct Query screen, focusing on the 2 of the 4 differences (highlighted green): o   Quick Measures o   Relationships Quick Measures This feature was first released in April 2017 and is available in Import Mode and Direct Query.  It enables a non-technical Business Analyst to create relatively complex DAX aggregations, with the use of a nice Wizard.  To access Quick Measures, right click on a measure or attribute and select Quick Measures.  Let’s try the same thing in Live Connection mode. You’ll notice that Quick Measures is missing from the list of options. I find it bizarre that Live Connection doesn’t support Quick Measures, especially when using a Tabular Cube as the connection.  The Power BI DAX language and engine are the same as Tabular, so you would think the two are compatible! Please vote for this feature to be added into Live Connection - Relationships There are two tabs displayed on the left-hand pane in Direct Query mode. If you click the highlighted tab, it opens a Relationships page – where you can begin to join datasets (from the database) together.  I created a manual relationship that joined DimEmployee and DimDate together – as shown below.  No relationships are created in the underlying SQL Server database, but instead stored within the Power BI model. In Live connection, the left-hand pane looks bare: There is no option to create any form of Relationship against the Live Connection Tabular Cube.  This kind of makes sense because a BI Developer would be the person responsible for creating relationships within an OLAP Cube.  I would argue that if you want the ability to mashup data or create your own relationships, you shouldn’t be connecting to a Cube in the first place. Coming Soon Check out Part 2 of my blog series - available here.  The focus of this article are the Add Hierarchy and Change to Import Mode features. Part 3 will conclude the Trilogy, where I go off-piste slightly and focus on the Data Models in Direct Query and Live Connection. Further Reading Other than the Power BI Blog, there are some other great pages out there too: o  Power BI Blog - o   Import Mode vs. Direct Query - o   Direct Query in Power BI - o   Live Connection in Power BI –   Contact Me If you would have any questions or thoughts, please leave a comment below.  My Twitter details are also provided. Twitter:                @DataVizWhizz

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:                @DataVizWhizz

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:  @DataVizWhizz

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:                                @DataVizWhizz