Pre Con: Azure Data Integration Bootcamp

🚨 Data Insight Summit – In person Conference in Chicago: 12-14th September 2022 🚨

Find the workshops here: Pre-Conference Full-day Workshops – Data Insight Summit

I will be hosting a pre con in collaboration with RADACAD in Chicago.

In this session we will be diving into most of the major moving parts of an automated enterprise BI solution, as per Microsoft reference architecture (Enterprise business intelligence – Azure Reference Architectures | Microsoft Docs). Azure Active Directory, Blob storage, Azure Monitor, Azure Synapse, Azure Data Factory, Azure Analysis Services and Power BI serving as key pillars in building a solid custom framework for automated data ingestion and analysis. Learn how to setup each of these services, how they interact and how to benefit from built in automation in Azure.
Leading by example, we will be tracing data from various sources on its journey through the Azure services, configuring them configure as we go along.

Supervising the PL-300 Labs at the Microsoft Business Application Cloud Week for Partners

BizApps Cloud Week

The conference is taking place April 4-8 and only requires a 2-hour commitment each day (apart from the labs). It is a virtual skilling event designed to teach attendees the practical applications of Dynamics 365 and Power Platform and prepare them for certification.
The agenda focuses on role-based certification tracks that are very relevant to my responsibilities as a what ever position they pursuit. This conference will have instructor-led courses, labs, exam prep sessions, and both live and offline Q&A.
Attending this conference would enable you to build your skills and implement them into customer projects in a meaningful way that would benefit my team and your company.
Please check out the conference registration page: Business Applications Cloud Week for Partners. The conference is free to attend with the only commitment being my time, as long as your are a Microsoft Partner.

My role in this event is supervising the labs. Specifically the labs around the PL-300 course. If you are unfamiliar with the PL-300 exam, it might be that DA-100 rings a bell!? The PL-300 certification replaces the DA-100 exam and introduces a set of changes. You can compare the skills tested here for PL-300 and here for DA-100. In a quick glance, the differences are as follows:

PL-300DA-100
Prepare the data (15-20%)Prepare the data (20-25%)
Model the data (30-35%)Model the data (25-30%)
Visualize and analyze the data (25-30%)Visualize the data (20-25%)
Deploy and maintain assets (20-25%)Analyze the data (10-15%)
Deploy and maintain deliverables (10-15%)

Obviously Microsoft has updated the content, but also moved the certification from one branch (DA) to another (PL). The DA branch only contained the DA-100 certification, as all other data related certifications either reside in the DP branch. Now it’s moved in with the other Power Platform certifications in the PL branch.

Back to the labs!

In this week of Business Applications, I am supervising the labs that run as homework for the classes in Power BI. These labs are similar, but not the same, as the ones that are available on some of the Microsoft Learn Learning Paths. If you are not familiar with these new and powerful capabilities of the online learning experience with Microsoft Learn, you should definitely check them out.

The labs are highly specialized towards a specific learning goal and of great use, when you want to fiddle about with a well refined use case.

Speaking at #SqlSaturday #Pittsburgh #770

September 29th I will be hosting two (2) sessions at the SqlSaturday event in Pittsburgh, Pennsylvania, US.

My first session will be on the basics of Power Query with some tips and tricks added to spice things up a bit. See details here.

My other session will be on Windows/Azure IoT and Raspberry PI. For that session, I used to carry a breadboard with my own poor soldering applied, but for this event I’ve bought a Grove PI starter kit which I am really excited to demonstrate as well. See more detail here.

The coincidence of a SqlSaturday and Pittsburgh Steelers playing in the same vicinity is something I experienced first time almost 5 years ago, as I went to my first SqlSaturday in Cambridge (which is a smashing event btw) where I got to see my first Steelers game as they played Minnesota Vikings at Wembley stadium

This time it will be on Heinz Field, taking on Baltimore Ravens – Classic arch rivalry in the AFC North. Wouldn’t be surprised if the final score was 2-0. But still very excited to go!

Speaking at the Power BI World Tour in Copenhagen

In just little over a week the Power BI World Tour will be stopping by in Copenhagen, Denmark. More precisely in Lyngby at Microsoft HQ. Copenhagen was luckily reelected for hosting the World Tour again this year, which I think will be very beneficial to the local Power BI adoption and community to say the least.

Copenhagen

Last time around I was honoured by having one of my sessions selected. This year I get to have two sessions. I am super excited about that!

My first session will be on Tuesday 11th about Power BI Best Practices. From the trenches of some of our own projects I have gathered a list of things to do in a project, to make your life easier.

My other session will be an Introduction to Power BI and Power Query (M). The query language is one of the few things of late that blown my mind in terms of capability and versatility. I will be showing you how to get started with the basics.

I am so looking forward to spending a couple of days with other Power BI Professionals, foreign and domestic!

Expanding Data from different functions in Power Query, in same column

My latest project work related project has offered me many first time experiences. Most prominent is, that this is my first fully fledged Power BI project. I have been playing around with Power BI for some time on a household basis and back in the summer of 2014 I ran some workshops with a selection of business people in Maersk Line.

The more curio first time experience is that the company we are developing this PBI Model for a company that was founded 368 years ago. Not months or weeks; years! They’ve been around and then some.

My main technical discovery with this project was the use of try … otherwise in M. I stumbled upon the construct via Gerhard Brueckl (b|l|t) in this blog post of his on error handling.

I found usage of this construct, not for direct error handling as such, but then again in some way it is error handling.

Data comes in many shapes and forms, and this project is no exception. In order for the project not to explode in hundreds of functions and exponentially as many queries, I had to come up with a way of containing this. So for educational purposes, let’s say we get three different file types from a single source. This single source needs to be presented to the end-user as one and only one source of data. Each of the three file types has a specific structure, which needs to be handled during import. There may be hundreds of files in total, but each file confers to one and one only format.

Generating the Steps

After going through a normal GUI import of each file type in Excel or Power BI Desktop, we can copy out the M code from the advanced editor. Once we have the code it’s fairly easy to convert into a function which we can invoke at will (read: on each file in a folder).

Let’s go over this, by a quick example. Given is a file containing data, for some specific period.

We obviously want to model this into a better shape, so reporting is made easier. We do so manually in Power BI by applying different steps. Here’s the result of my steps.

How I got there is not really important for the sake of this demonstration, only that I can pick up the code behind by looking at it in the advanced editor (apologies for the Danish labels). 

The code will look something like this

let
    Kilde = Excel.Workbook(File.Contents("C:\Users\jve.CMS\OneDrive - CatMan Solution A S\eksempel.xlsx"), null, true),
    Tabel1_Table = Kilde{[Item="Tabel1",Kind="Table"]}[Data],
    #"Ændret type" = Table.TransformColumnTypes(Tabel1_Table,{{"EAN", Int64.Type}, {"Date", type date}, {"Copenhagen", Int64.Type}, {"London", Int64.Type}, {"Tokyo", Int64.Type}, {"Berlin", Int64.Type}, {"Paris", Int64.Type}}),
    #"Fjernet pivotering af andre kolonner" = Table.UnpivotOtherColumns(#"Ændret type", {"EAN", "Date"}, "Attribut", "Værdi"),
    #"Omdøbte kolonner" = Table.RenameColumns(#"Fjernet pivotering af andre kolonner",{{"Attribut", "Store"}, {"Værdi", "Value"}})
in
    #"Omdøbte kolonner"

or as in the screen shot


Create a Function in M

Turning the above code into a function is actually pretty straight forward. We know we are to iterate a folder full of files, so we know we are going to have the location of each file. Our function will then take a file (location) as parameter.

This is done like this:

let
    #"Import" = (file) =>
let
    Kilde = Excel.Workbook(File.Contents(file), null, true),
    Tabel1_Table = Kilde{[Item="Tabel1",Kind="Table"]}[Data],
    #"Ændret type" = Table.TransformColumnTypes(Tabel1_Table,{{"EAN", Int64.Type}, {"Date", type date}, {"Copenhagen", Int64.Type}, {"London", Int64.Type}, {"Tokyo", Int64.Type}, {"Berlin", Int64.Type}, {"Paris", Int64.Type}}),
    #"Fjernet pivotering af andre kolonner" = Table.UnpivotOtherColumns(#"Ændret type", {"EAN", "Date"}, "Attribut", "Værdi"),
    #"Omdøbte kolonner" = Table.RenameColumns(#"Fjernet pivotering af andre kolonner",{{"Attribut", "Store"}, {"Værdi", "Value"}})
in
    #"Omdøbte kolonner"
in
    #"Import

Notice only a few minute changes, wrapping the step a let #”Import” = (file) => … in #”Import” is almost all it takes. We only need to apply the parameter to the steps already defined. So we swap the fixed file path with the parameter file. Now we are set to create a new query that iterates files in a folder and invokes this new function, taking the file location as parameter.

Ones this has been done for all of the different types, three in this particular example we need a way of appending the data from the different queries to one another. This is where the try … otherwise structure comes in handy.

Imagine we have a function available in the model for each of the three file types. Let’s call them fnImportFileType1, fnImportFileType2 and fnImportFileType3.  A prerequisite for this to work properly is that we know for a fact that no one file will produce any meaningful result from more than one (1) of the functions. Otherwise we would have duplicate data, and while we love data, duplicating it should be done with careful consideration.

So rather than adding a column for each of the three file types, or simply generating a query for each one and then appending each of those in the end, we can accomplish this with a little cleverness. Now, some might argue that files should be sorted and only those relevant for a given function is actually iterated and parsed – but the world is not always perfect or maybe there is no way of sorting the files (in a reasonable amount of time).

So, without further ado, here is a way of appending data from multiple sources/functions into one single column which can be expanded, as two steps in M.

    #"Added Column" = Table.AddColumn(<insert step name>, "Custom", each try #"fnLoadFileType1"([Url]) otherwise try #"fnLoadFileType2"([Url]) otherwise try #"fnLoadFileType3"([Url]) otherwise null),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Column", "Custom", {"SalesUnits", "SalesValue", "EAN", "VAT Incl."}, {"SalesUnits", "SalesValue", "EAN", "VAT Incl."}),

For each row, we invoke first fnLoadFileType1, if that fails we load fnLoadFileType2 and if that fails we invoke fnLoadFileType3, finally we return null if all loads fail to produce a result.

Conclusion

While I have no idea if this is killing performance in the long run (it doesn’t now, going through 500 files) I get that this is maybe not the perfect situation. However, I am able to produce a (valid) result for our client within a narrow time frame – which is something they really love.

I’d love any input on this, as I am learning as I go along.