Month: December 2020

Dynamically changing title names in Power BI

/ Mitchell Pearson / Leave a comment

Hey Everyone! I’m excited that I’m finally getting this blog done! Also, I’ve embedded the YouTube video below Smile

Also, in other news, after 8 years of blogging I finally decided to make some kind of logo, it’s a little unique but I like it! Especially how I was able to sneak the board game in there, because I love board games!

Original on Transparent

Figure 1 – New logo

Here is the video posted on YouTube if you want to watch the steps in action.

What is a Dynamic Title in Power BI?

The idea around a dynamic title is that the name of the title changes automatically to reflect any filters that may be applied. In the automated gif below, watch how the title name changes as I drill down into the chart visual.

DynamicTitles

Figure 2 – Dynamic title, animated gif

Creating Dynamic Titles in Power BI with SELECTEDVALUE() Function

Creating the dynamic title you saw in Figure 2 above requires a little DAX, not much. First, create a new calculated measure and give it a name, in the YouTube video I called this measure Dynamic Title. Then I used the following expression:

image

This is a simple expression which will return the name of the country if only one country exist in the filter context, if not, it will return the text “All Countries”. In this example I am navigating a hierarchy, therefore, I want the title name to change for each level of the hierarchy as new filters are being applied. The following screenshot is the completed code from the video:

image

Conditional Formatting in Power BI on the Title of a visual

The final step is to use the calculated measure on the visualization with the following steps, see animated gif for reference:

  1. Select visual and navigate to the formatting pane
  2. Find the title category and expand it.
  3. Click on the fx icon next to title
  4. Select field value for Format By
  5. Select your measure from the drop down list for Based on Field.
  6. That’s it!

As always, hope this post helped!

2_Conditional Formatting


Working with Parameters and Expressions in Azure Data Factory

/ Mitchell Pearson / Leave a comment

Hey All! Just in time for the holidays I’m uploading another Azure Data Factory video to YouTube. In this video we specifically look at how to use Parameters in Azure Data Factory to make your datasets and pipelines dynamic and reusable! In addition to parameters and expressions we also take a look at the Lookup, For Each and Execute Pipeline activities. This video is very informative, touches on a lot of different pieces and I hope you enjoy it!

Parent / Child Design Pattern in ADF

The Parent / Child design pattern is a popular design pattern for ETL processes and has been for many many years. This gives you that compartmentalization (if that’s a word) of your code making it more reusable and also giving you the ability to easily scale up and scale down by increasing or decreasing parallelization of your workers.

In this scenario the Parent pipeline will determine what work needs to be done and then pass that work to the worker pipeline. In my video I show how we use the Lookup activity in Azure Data Factory to get the list of items to process and then the list can be parsed out.

Azure Data Factory Lookup and Foreach activity

image

This scenario is retrieving the work from an Azure SQL Database, therefore, I use the lookup activity to retrieve the work. However, keep in mind you could use many different activities to serve this purpose, for example, I could use the metadata activity to get the list of files in a folder that needed to be processed. See my video on the metadata activity here:

https://www.youtube.com/watch?v=zm7ybXmUZV0

The For Each activity is used to parse through the list / array that was created by the lookup activity. The For Each activity will perform a set of activities, defined by you, for each item that is returned in the list or array provided.

In this example, we are simply executing the worker pipeline passing in the current schema and table name of the table we want to process!

Execute Pipeline activity in ADF

The child pipeline or worker pipeline is where all the magic happens. In development, we make the datasets dynamic by parameterizing their connection so that the connection can be changed at run time based on the parameters passed in from the parent to to the child. In the following screenshot you can observe that the dataset is connecting to an Azure SQL Database and the schema and table name are determined by parameters at run time!

image

For specifics on the setup, orchestration and execution of this design pattern watch the video Smile As always, thanks for reading my blog and have a great week!

Introduction to Wrangling Data Flows in Azure Data Factory

/ Mitchell Pearson / Leave a comment

Hello! It’s been a while since I’ve done a video on Azure Data Factory. To get back in the flow of blogging on ADF I will be starting with Data Flows, specifically Wrangling Data Flows.

The video can be seen here:

What are Wrangling Data Flows in Azure Data Factory?

Wrangling Data flows are a method of easily cleaning and transforming data at scale. huh?

Wrangling Data Flows uses the M query language and the UI experience provided by the Power Query Editor in Power BI Desktop. This is a brilliant move by Microsoft to include this technology in Azure Data Factory. Just think of the hundreds of millions of people who currently are transforming and cleaning their data in Excel or Power BI Desktop. Now they can take their self service ETL (extract, transform and load) skills to the enterprise level with ADF.

What makes it scalable? Power Query Editor at Scale.

Wrangling data flows allows the developer to use the graphical user interface to do all the hard work with minimal to no code. But in the background all of your UI steps are being converted to the M language. At runtime, Azure Data Factory will take that M code and convert it to Spark and then run your data flow against big data clusters. This means as your data volumes grow, you should experience consistent performance!

Are there any limitations with Wrangling Data Flows?

Yes… quite a few actually. Wrangling Data Flows are still in preview at the time of this blog and the related YouTube video. Currently there are quite a few operations that just aren’t supported. The most obvious of those operations being promoting header rows and pivoting data. I hope that these features will be available once the product is in GA.

https://docs.microsoft.com/en-us/azure/data-factory/wrangling-data-flow-functions#known-unsupported-functions9

As always, thank you for reading my blog and watching my YouTube videos! Have a great day!!

Other Azure Data Factory resources!