Power BI RoundUp – Power BI Desktop Dec Update – Dataflows Integration with your own Azure Data Lake – Business Application 2019 – Developer Update Nov 2018 – Windows App Update

It appears that there is one last massive push from the Power BI team before the end of the year.

I would consider this my early Christmas present, so please enjoy and read below.

Power BI Desktop Update Dec 2018

Once again it is another big update to Power BI desktop.

Reporting

I really like the smart guides that are available, this makes the visuals line up better and make the entire development experience that much easier. To me it is very similar to what you get in PowerPoint.

There have been a whole host of really cool updates for ArcGIS in both the free and paid versions where you can find similar items, as well as having up to 5000 map data points in the paid version.

It is great to see such a great focus on accessibility features being implemented into Power BI, and this month they have extended this with the field list accessibility support.

You can now set the tab order on objects on a page, this is really good not only for accessibility but I have had times when the mouse has not been working and by setting this up, it allows me to easily move between items. You can also move items and disable them from being in the tab order.

There now is tooltips for buttons, which was suggested by my good friend Matt Allington and it certainly makes it a lot easier to get more context on what the button is supposed to be doing.

Icons showing the visual interactions as well as all the other images now are in an SVG format, which will make it scale better. I personally like the updated icons

Analytics

There now is support using Q&A for LiveConnection SSAS (SQL Server Analysis Services) databases. The one thing to note is it is currently only supported for Tabular Models with the compatibility mode of 1103 and above.

The above is a preview feature, so you will have to update it in the Preview features under Options.

Modeling

The DAX formula bar has received 2 updates.

The first one is now you can zoom in and out, which is great when you are presenting or when you want to see more of your code on a high-resolution screen.

Second, is that the DAX formula bar will expand to the size of your screen, allowing you to see more of your DAX code, when there is a lot of code

Yet another accessibility feature is that you now can access the data view using CTRL + F6 and you can navigate all the items in the data view.

Data Connectivity

This month there are 2 new connectors.

The AtScale Beta Connector and the Essbase Beta Connector

Data Preparation

When using the Fuzzy merge there now is an option to select the TopN matches when trying to match using the Fuzzy merge logic. This is great to ensure that you do not return too many matches

Other

High contrast support is now available for all panes and report footer

And finally, there is an improved keyboard shortcuts dialog as shown below.

Here are the blog post details, and I would recommend watching the YouTube video: Power BI Desktop December 2018 Feature Summary

Data Flows Integration with your own Azure Data Lake

There has been a lot of interest with Power BI Dataflows since it was released.

That latest release now allows you to unify data across Power BI and Azure Data Lake.

This can now be done by creating CDM folders in the Azure Data Lake, and once that has been created not only can that be used by Power BI, but it can also be used by Azure Data Factory, Azure Data Bricks, Azure ML and no doubt more integration points over time.

There really is a wealth of information in this blog post.

Not only that but there also is a full tutorial which will allow you to test and end to end setup. That is SO COOL! (And I am going to set some time aside to get it working)

Here are the blog details: Power BI Dataflows and Azure Data Lake Storage Gen2 Integration Preview

Business Applications Summit 2019

The dates for the Business Applications Summit have been announced on 10-11 June 2019, at this year it will be in Atlanta Georgia.

You can already buy tickets for this event, and I am planning on attending next year again. I found that going to these events are an invaluable way to do a lot of learning and networking with some incredible people.

All the details are here: Microsoft Business Applications Summit is back – join us June 10-11, 2019 in Atlanta!

Developer Update Nov 2018

There have been a lot of updates in the Power BI Developer space in November.

Power BI Embedded in Azure

You can now have Paginated reports and data flows in Power BI Embedded.

Not only that but there are also 2 new metrics to monitor your Power BI Embedded which are Memory per Workload and QPU per Workload. Both are important because now there are potentially 3 workloads in Power BI Embedded which would be Analysis Services, Paginated Reports and Dataflows.

There is also now Azure Health monitoring to ensure that all the resources are available as shown in the image below

There is now also the capability to have zero downtime when scaling your resources when scaling up or down.

This is done by using a script which creates a temporary capacity resource, and moves your current workspaces to this temporary capacity resource, until the scaling up or down is complete and then moves it back.

Embed Capabilities

Usage metrics are now available for embedded reports and dashboards as shown below.

Automation and Life Cycle Management

You can now create a new App Workspace with the Power BI REST API

Custom Visuals with additional purchases are now supported, with In App Purchases

Here are all the details: Power BI Developer community November update

Windows App Update

There has been an update to the Windows App for Windows PC’s, Laptops & Tablets where you can now enter presentation mode which will allow you to have it as you are presenting.

Not only that but if you have a pen and compatible device you can also Ink this onto the report.

The blog details are here: Presentation mode in Power BI Windows App

Converting Multiple Items into One Row in Power Query

I was working with some external data where I had a list of Australia postal codes, the challenge is that there are multiple suburbs that are part of one postal code.

Below I am going to show you how I took the multiple suburb names and put them into a single row, which would then allow me to have a single postal code per row. Which would then allow me to join this to my table using a relationship in my data model.

This is what the table looked like before the transformation 

 

This is what the table will look like once I have completed the steps below 

 

How I did it  

·         I first selected my column called postcode, and then in the Transform ribbon clicked on Group By  

o     

·         I then configured the Group By as shown below.  

o      

o    What I am doing here is to group by the postcode, and then putting the remaining rows effectively into a table by using the “All Rows”  

o    This resulted in a single row per postcode  

§    

·         I then went into the Add Column ribbon and clicked on Custom Column  

o      

·         I then put in the following code into my Column name “Suburbs” 

  • NOTE: Even though I cannot see the column called [locality] any more in the Available columns, I can still use it in my Custom Column.
  • This resulted in the following in my table
  • I then clicked on Expand on the Suburbs column and selected “Extract Values”
  • I then selected the delimiter to be a comma for the list of values
  • I could then see my new Suburbs column with the extracted values being comma delimited
  • I then removed my unwanted column called AllData and renamed the column headers to get my final result.

Conclusion 

I have shown once again how versatile Power Query is and also that very often things can be done via the GUI which makes it intuitive and easy to shape your data.

If there are any questions or comments, please leave them in the section below.

Thanks once again for reading and I hope that this will be useful to you at some point!  

Power BI RoundUp – Ask a follow up question, Help Improve Power BI

As I am typing this I am wondering if tomorrow I will have to update it with the new Power BI Desktop features, or if this will be coming out next week!

Here is my weekly Power BI RoundUp

Power BI – Ask a follow up question

If you are not aware there is now new functionality in Power BI to be able to ask a related question based on your first question that you asked. This is really powerful stuff.

If this is something of interest to you here is the blog post: Ask a question using natural language updates

Power BI – Help Improve Power BI

If you would like to help improve Power BI, then here is your chance to take the survey.

I have taken the survey myself.

Not only that if you live in the USA you could win a $50 gift card.

Here is the blog post: Help improve Power BI – quarterly survey

Power BI – How to Configure “Send Refresh Failure Notification email to Distribution List (Multiple Users)” for Dataset refreshes

As it currently stands when I setup a dataset refresh it only emails me when it fails.

I was recently at a customer where there is a team of Power BI Developers, and I wanted to find a way that all the developers would get a copy of the dataset refresh failure email.

This would ensure a single point of failure if the person receiving the email is not in, as well as to make the entire team aware of any issues if someone in the business had a question.

Fortunately, this is relatively easy to setup and thanks to the awesome Power Platform I am going to show you, how to use Microsoft Flow to get this working.

Pre-Requisite

The only pre-requisite is that there is an existing email distribution list set up in your organisation for the people you want to notify.

This is an example of how my distribution list is below

Creating the Microsoft Flow to Distribute Emails

I will show you how using Microsoft Flow, I can then distribute the failure emails to my distribution list that I created above.

  • I went into https://flow.microsoft.com
  • I then clicked on My Flows on the left hand side, then at the top clicked on New and selected “Create from Blank”
  • I then clicked on Create From Blank again
  • In the search box I then searched for “When a new email arrives”
  • I then selected “When a new email arrives” for Office 365 Outlook
  • If you have not connected to Office 365 Outlook, you might be prompted to connect.
    • I have already connected before so when I clicked on the Ellipses on the right hand side I could see my existing connection
  • Click on the Show Advanced options, because this is where I am going to configure which emails to send to my distribution list.
  • I then configured the sections below:
      • This is to ensure that it is emails that I receive from the Power BI Service
    • Subject Filter: Refresh failed:
      • This is to ensure that it will only forward on emails where the subject contains “Refresh Failed”
  • I then clicked on New Step
  • I then searched for “Forward an email”
    • I selected “Forward an email” for Office 365 Outlook
  • Then where it says Message ID, I clicked into the Text box, which then opened the Dynamic content.
    • I scrolled down and selected Message Id as shown below.
  • Then where it has got the To, I put in the email address of the distribution list I created earlier.
    • I also added a comment to the forwarded email as shown below.
  • I then clicked Save to say my Flow
  • On the top right-hand side, it saves it with a default name
    • I clicked into this area and renamed the flow to: Power BI dataset refresh failure email distribution
  • And finally, on the right hand side there are options to Save, Flow Checker and Test
  • I do always recommend testing the Flow to make sure it works as expected.
    • In this situation you can possibly select to use Office 365 data, if you still have emails in your Inbox
    • I personally have found this to be really stable and have received the emails the next time it failed.

Conclusion

I have shown you how to setup and configure the Microsoft Flow to distribute dataset refresh failure emails.

Here is a link to my flow, which you can use to import into your own Microsoft Flow Environment where you will need to configure the required Outlook settings.

PowerBIDistrributionFailureEmails_FourMoo.zip

As always if you got any questions or feedback please let me know. I am most certainly not a Microsoft Flow expert, so if there is an easier way to achieve this please let me know.

Thanks for reading!!

Multiple conditions for a conditional column in Power Query

I am often working on datasets where there is more than one condition for a conditional column.

And whilst the GUI based Conditional column is really good, it currently does not have the capability for multiple conditions.

In this blog post below, I will demonstrate how to achieve this.

In my example below, I have a table that has got Bike Brands and Types.

I now want to create a rating based on both the Brand and Type.

I do this by creating a Custom Column

The way the multiple conditions work is based on the following pattern:

if [Column Name1] = “Condition” and [Column Name 2] = “Condition” then “Result”

else if [Column Name1] = “Condition2” and [Column Name 2] = “Condition2” then “Result2”

else if [Column Name1] = “Condition3” and [Column Name 2] = “Condition3” then “Result3”

else “Unknown Result”

This is shown with my working example below.

Which results in my table now having a new column called Rating which has the multiple conditions for my conditional column.

Another thing to note is that I could also do a range between two values which is essentially multiple conditions for a conditional column.

What I had to first do was to change the Amount column from Text to Whole Number, so that my conditions would work. After which I then used the following conditions

Which resulted in getting the banding that I was after.

And finally, (the last one I promise!), is where I can use the flexibility within Power Query to convert the Amount value on the fly from a Text value to a Number value for my conditional column.

This will allow me to keep my column in my table as a text value.

Conclusion

I hope that you have found this blog post useful and as always if you have any suggestions or comments please leave them in the section below.

Thanks for reading!

Power BI RoundUp – Help Shape the Future of Power BI – On-Premise Update for Nov 2018 – Modeling View – Analysis Services is 20 years old!

Here is my weekly roundup, and whilst it has not been as busy as the past few weeks, there are still some awesome updates.

Power BI – Help Shape the Future of Power BI

I always think it is really important to provide feedback, especially when they are asking for assistance.

You can click on the link above to take the Survey.

Personally, this gives me the opportunity to be able to give feedback and hopefully help improve and drive the product forward.

You can find the details here: Help shape the future of Power BI

Power BI – On-Premise Data Gateway Update for Nov 2018

You can click on the image above to download the latest version of the On-Premise Data Gateway.

In this months, updates are bug fixes, improvements and the November version of the Mashup Engine, which runs all the Power Query steps.

All the details can be found here: On-premises data gateway November update is now available

Power BI – Modeling View

As shown above there is the new Modeling View in Power BI, and this blog post does a quick overview of the new features, as well as a GIF to show you it in action.

Blog details can be found here: Modeling View in Power BI Desktop

Analysis Services is 20 years old

Whilst it might not appear to some people why this is so significant, this is because the underlying analytical engine in Power BI desktop is a version of Analysis Services.

This video is awesome to see people both from Microsoft & other awesome people who have been using analysis services for a VERY long time.

I really enjoyed watching the video, I would recommend watching it.

All the details can be found here: Analysis Services is 20 years old!

Refreshing data in Power BI from SQL Server Read-only or Secondary Replica’s

I have seen a lot of questions in the Power BI Community asking how to connect to a SQL Server Read-Only or Secondary replica, and whilst this is not currently available in the Sql.Database M connector, it is available in the OLE DB connector.

NOTE: This could potentially be used for a whole host of other OLE DB providers

NOTE II: This only applies to Imported datasets at the time of writing this.

In my example i am going to be using the OLE DB Connector for SQL Server Native Client 11.0

I will explain below how to create the connection, as well as how to configure it in the Power BI On-Premise Data Gateway.

Connecting to the Data in Power BI using the OLE DB Connector

  • The first thing I do, is to connect to the data, by clicking on Get Data and then finding my OLE DB Connector
    • I then click on Connect
  • The great thing about Power BI, is that after I click on Connect, there is a button to Build the connection string, so I click on Build
  • This brings up the Data Link Properties Window
    • I click on SQL Server Native Client 11.0
    • I click on Next
  • I then put in my Server Name, select Use Windows NT Integrated Security and select my database
    • NOTE: Depending on how your Gateway authenticates to SQL Server will depend on what you put in here.
    • NOTE: I always put in the FQDN name for the SQL Server instance.
    • I then click on Test Connector to make sure that I can connect.
  • I then click on All, where it says Application Intent, it will default to READWRITE
    • NOTE: This is the IMPORTANT part where I want to make sure that it will use the Read-Only replica or secondary replica
    • I clicked on Application Intent and then clicked on Edit Value
    • I then clicked on Ok to complete the changes
  • And then I clicked on Ok again.
  • I can now see my connection string built for me
    • NOTE: If I scroll across I can see and confirm that I have set the Application Intent to ReadOnly
    • NOTE: Make a copy of the connection string above. This will be used when creating the data source in the On-Premise Data Gateway.
  • There is an option under Advanced Options, if you want to put in a SQL Statement, that is totally up to you, or you can click Ok.
  • I was then prompted for the OLE DB Provider credentials.
    • As far as I understand this is so that the credentials are stored separately from the connection string and not stored in clear text.
    • I selected Windows and then “Use my current credentials”
    • I then clicked Connect.
  • I was then prompted with the Navigator which allowed me to select the table that I wanted
    • I then selected the table that I wanted and clicked Edit
    • NOTE: I always prefer to Edit the queries in the Power Query Editor before loading them into the data model.
  • I then added it to my data model
  • And uploaded it to the Power BI Service.

Creating the Data Source in the Gateway in the Power BI Service

NOTE: You will have to ensure that you have got the permissions to add data sources to the Gateway in the Power BI Service.

  • I went into the Power BI Service and clicked on Manage Gateways.
  • I found my associated Gateway, then clicked on the Ellipses and selected Add Data Source
  • I then configured it with following:
    • Data Source Name
    • Data Source Type
    • Connection String (NOTE: You can get this from your PBIX file if you did not copy it from the earlier step
    • Authentication Method, I chose Windows. Yours possibly will be what you connected to in Power BI Desktop.
    • I then put in the username and password
    • Finally, under Advanced settings I selected Privacy Level to None
    • I then clicked Add
  • I then got the confirmation that it was successfully created
  • I then went into my App Workspace where I had uploaded my PBIX, then into the settings and configured it to use the Gateway.
    • I then clicked Apply.
  • I tested the refresh and it successfully refreshed
  • My final test was to sit with the DBA and ensure that when my data was being refreshed that it was reading from the read-only/secondary replica which it was.
    • I always suggest double checking to make sure that it is working as expected, and to not assume that it is working.

Conclusion

In this blog post I have showed how you can now connect and import data from a read-only or secondary SQL Server replica. This has really been great for some of my customers who want to ensure that the data refresh queries will not impact the data warehouses or production systems.

Please feel free to leave any comments or suggestions, I do enjoy getting them.

Additional Note

I needed to ensure that I had the latest versions of the OLE DB Providers installed for this to work as expected.

I would suggest making sure if you are going to use the native OLE DB provider that you install the latest version from here: Microsoft OLE DB Driver for SQL Server

Power BI RoundUp – (Nov 2018 Desktop release – AI Capabilities for Power BI)

Well this month has sure been a busy month with a whole host of additional updates for Power BI. I personally am really looking forward to the next few months to better understand how all of this will evolve and assist customers on leveraging their data assets.

Power BI – Nov 2018 Desktop

This was yet another massive update to Power BI Desktop with a lot of the most requested features being released. As I do every year below are the features which I would like to highlight in this release. If you are interested in all the details please read the entire blog post from the Microsoft Power BI team.

Reporting

As shown in the picture above there is now the option to have expand and collapse buttons for tables and matrix. You can also do this from the right click context menu. And a great feature when you do not want to expand all the rows, but rather a particular area.

Another new feature is the capability to be able to copy visuals between PBIX files. This will work well if you have two PBIX files that have the same datasets, then the visual will be copied across. If it does not, then you will get a warning with regards to this.

The new filtering experience is really a great new feature. Not only can you now let users easily change the filters on the data as they require. It is also customizable in terms of the look and feel. Along with that, you can also select which filters can be changed and which filters are locked.

As you can see above, when you are on a visual you can hover of the filter icon and it will show you what is filtering the visual. That is AWESOME

As you can see above there are now additional Hotkeys for accessibility options, which is great to see that they are expanding on these capabilities.

The above now also supports keyboard navigation, screen readers and high contrast.

Analytics

There is a new feature where you can now use colour saturation on visuals with conditional formatting, in all 3 forms which is by colour by scales, colour by rules & Colour by field.

It applies to the following visuals below.

  • All variants of column and bar charts
  • Funnel chart
  • Bubble & filled maps
  • Shape map (preview)
  • Treemap
  • Scatter chart

They have expanded the Q&A features to now allow you to be able to ask related questions in the Q&A explorer. You do this by clicking on the “Ask a related question” which will allow you to keep asking questions based on your previous question.

The blog post also gives a bit more details on terms of what additional question types you can ask.

Modeling

When you open up Power BI Desktop Nov 2018, you will see the above button on the right hand side. This new modelling experience allows you to do the following:

  • Customize and save multiple diagram layouts
  • You can see the modelling options through the field properties pane and field list.
  • Capability to apply settings to multiple columns at once.
  • And finally, the option to create display folders, which I know is something I have been waiting for, as well as a lot of other people have been asking for this feature.

Composite Models are now Generally available in the Power BI Service. Amazing how quickly that went into GA!

There are 3 new DAX functions which are below.

Daniil already has got a great blog post explaining how to use this new IsInScope DAX function : New DAX function: ISINSCOPE

Custom Visuals

Below are the actual visualizations for the new custom visuals that have been released. If you are looking for more details, please read the blog post from the Power BI Team (link at the end of this)





Data Connectivity

As shown above there is now a beta connector to Azure DevOps Server to import and report on your Azure DevOps data.

There have been improvements to the PDF connector to allow you to specify a start and end page of your PDF file to look for tables within the PDF file.

The Azure Consumption Insights connector now includes Budget and Reserved Instances.

All the blog post details can be found here: Power BI Desktop November 2018 Feature Summary

Power BI – AI Capabilities

There is a new private preview of some new and exciting Power BI AI features that are coming.

The new capabilities are looking to include the following:

  • Using Azure Cognitive Services which will allow for image recognition and text analytics directly in Power BI.
  • Key Driver Analysis to better understand what drives key business metrics
  • Automated Machine learning models directly in Power BI
  • And finally, the capability to be able to bring your own Machine Learning models into your Power BI datasets.

The future of AI and Power BI sure does look bright.

You can find all the details here: Announcing new AI Capabilities for Power BI to make AI Accessible for Everyone

Power Query – A function to remove spaces within Text values

UPDATE: 19 Nov 2018

With the recent announcement of dataflows in the Power BI Service, I see more people will be looking to better understand and leverage dataflows by using the M language which is available in Power BI Desktop, Power Apps and Microsoft Flow.

I had a great response to this blog post, and both Ted and Daniil had a much easier solution to remove spaces from the data in a column. I am not sure why this originally did not work for me, but I am always happy to learn from others. As well as find an easier way to achieve the same outcome.

The solution is all that you need to do, is to right click your column and click Replace Values, as you can see below I am searching for the space and replacing with no space

 

 

 

BELOW IS THE ORIGINAL BLOG POST

The Power Query function below will remove any spaces that I have in a text field.

I got the original Power Query function from Ken Puls blog post Clean WhiteSpace in PowerQuery which he does a great job of removing leading, trailing or multiple spaces within text.

My requirement was to remove any spaces within the text too.

I created a Blank Query in the Power Query Editor and named it fx_ReplaceSpaces

Below is the code that I actually used.

And here is what it looks like with some sample data

And this is the output, where I wanted all spaces removed

There might be a more elegant way to achieve this, so if anyone has got any suggestions please let me know, I will be happy to test and update this blog post.

Power BI RoundUp with SQL Pass updates – Dataflows – Paginated Reports (SSRS) – Premium Metrics App with Data Flows & Paginated Metrics – Paginated Reports in Mobile App – Mobile & Service Update for Oct – Bonus features coming to Power BI Desktop

As expected this week with SQL Pass, there has been a whole host of new capabilities and features in Power BI, so please see my roundup below with the relevant links below.

And even though there is a lot of announcements here, we are still waiting for the update to Power BI Desktop!!

Power BI Dataflows

One of the biggest updates and releases to Power BI is dataflows. I personally think that this is a MASSIVE differentiator from other players in the Self-Service space.

The first thing I would like to highlight is that this is available to both Power BI Pro and Premium Customers. Below is a table of the key differences.

Dataflows allows you to now build data purely inside the Power BI Service, that stores the data in Azure Data Lake Gen2.

There are so many advantages to this:

  • Not only does it allow the business user to easily access data and use it in their data model as a data source (like SQL Server)
  • It also allows for the linking of entities (Premium feature)
  • It allows for Incremental Refreshing (Premium Feature)
  • Linking of entities which works like Excel (Premium Feature)

I would highly recommend that you read the blog post here: Introducing: Power BI data prep with dataflows

And here is the link to the Whitepaper: Understanding Dataflows in Power BI

Power BI Paginated Reports (SSRS)

There is now the capability to be able to use SSRS in the cloud. This is really great news and I am certain that there are a lot of customers who have been patiently waiting for SSRS to be available in the cloud.

It is now available in Power BI Premium. And along with this out of the box there are a lot of features that are already there, and some additional features which will come later. No doubt it is a very different set of requirements to build something that will work in the cloud vs an On-Premise implementation.

Some of the potential new capabilities which will be coming in the future to Paginated reports are:

  • Support for scheduling and sending paginated reports using e-mail subscriptions and having attachments
  • Embedding in third-party applications
  • Authoring reports against Power BI datasets
  • The ability to seamlessly drill through from a Power BI report to a paginated report in the service

All the details can be found in the blog post: Public Preview of Paginated Reports in Power BI Premium Now Available

Power BI Premium Metrics App includes metrics for Dataflows & Paginated Reports

It is incredible to see the pace of innovation from Microsoft and the Power BI team. Even though both dataflows and paginated reports have just been released you can now already use the Power BI Premium metrics app to see metrics for dataflows and paginated reports.

You can now see the following metrics for Paginated reports

  • Total views of the report with average for row count
  • Total time spent on the reports between retrieval/processing/rendering
  • Split by hour, dataflow name, and workspace name

The following is also available for data flows

  • Total refresh count
  • Refresh reliability
  • Average/max duration and average/max wait times of dataflow refreshes by dataflow name and workspace name

You can find more details on the Power BI Premium Metrics App here: Paginated report metrics and dataflow metrics now available in Premium Capacity Metrics app

Power BI Mobile App with support for Paginated Reports

If you update your Power BI Mobile App, you will now have the capability to be able to see paginated reports directly within your Power BI mobile app.

Once again it is incredible to see all the new functionality released across the entire platform, not only to the Power BI Service, but to the mobile app also.

It is available in the Android, iOS and Windows Apps

More details can be found here: Power BI Paginated Reports also available in Power BI Mobile Apps (preview)

Power BI Mobile and Service updates for Oct 2018

A lot of the updates for the Power BI Mobile and Service app have already been covered in other blog posts.

Below is a list of the updates with the relevant links