The encounter

At May 2, 2020 I bumped into a LinkedIn post from Alex Powers about a #30DQUERY challenge.

On my road to learn more about Power BI and searching for challenges to learn how to use the tool I was like “lets join this challenge”.

My interest in Power BI and my past track

In the first quarter of 2019 I followed the Microsoft Data Science Track and one of the 11 courses was analysing data with Power BI. That is when I fell in love with the program.

I went looking for more information. I got myself into a weekly online challenge to visualise a given dataset, with www.makeovermonday.co.uk. They were fun and I learned a lot. I went into the direction of how to visualise the data in a dashboard in a nice and need way. #StoryTelling with data, I wanted to know more about.

During visualisation I realised I sometimes needed to change the given dataset. So, I learned how to add columns, measurement and I started to learn DAX from www.sqlbi.com.

When I saw this #30DQUERY challenge on LinkedIn I was like, I know what queries are and I want to do a Power BI based challenge. Get to know people who use the tool, learn from them. So lets jump in.

Since March 2020 I have a new job. This employer is using Business Objects and BI Publisher, two new tools for me. So the interest in Power BI is personal.

The first day’s

Import a dataset via the SQL-server, not all columns are needed, new columns need to be made and filters applied. Use the Power Query Editor to shape the dataset in a way that you want it to load.

Not to difficult with the knowledge I have built up with DAX.

The first 14 days I wrote blog posts about my experience, see www.kiwimaori.nl. After that the challenge became more time consuming, more difficult and I needed to check into other submissions to find out how to use the GUI or to write code.

Questions during the first day’s

• Alex is talking about query folding; I know query and I know what folding is. But what is it when these two words are used a 1 expression?
• Why is the Power Query Editor giving me code’s I don’t know when I start typing DAX-code?

Time to do a google search to find some answers.

Started with the playlist form Alex called An Introduction to Power Query M

Found a You Tube movie from Pragmatic Work about the Power Query Editor and on May 18 there was a live webinar about Query Folding by Nicky van Vroenhoven and dataMinds. This all helped to find my answers.

• I found out that query folding means that the selection of the data happens in the DataBase and not in the engine of Power BI. So, it speeds up the loading of data.
• The Query Editor is not responding to DAX-code, because that language is not used in Power Query. In the back up part of Power BI you need the language M.
  Oh why has that not been told in the Analysing with Power BI training. Because in that training, we also have been told how to import and change date with the GUI of the Power Query Editor.

Question about how to load more entities into 1 Query

I kept seeing in the answers that others loaded more than 1 entity into the same Query, while I had not a clue how to do that. So, halfway the challenge, I asked the question in twitter at the rest of the team. Steven Bitaxi gave my explanations and now I know how to do it. I even went back in time to correct some of the solutions, where I had been using merge and the folding had been broken because of that.

Wrap-up meeting

May 31, 2020, we had an online team Wrap up meeting organised by Alex. A group of us where online and we shared our experience for almost 2 hours. It was great to see each other and to hear the story’s and backgrounds.

Take away

I learned a new language. Power Query #M.

I learned that the more you can do outside Power BI, the faster the engine inside the application stays.

I can use the produced Native Query to build up my SQL skills, that I need during my job.

I met new people who I can talk to whenever I have Power BI questions.

Some extra’s

Because I saw solutions in GitHub, I started to become more interested in GitHub and followed a lab GitHub training to see what is possible for me how to start using GitHub

I decide to blog not only on my own website, but post this as an article in LinkedIn.

Tip for trainers who introduce new people to Power BI

Introduce them to visualisation, DAX and to Power Query M. With visualisation people will fall in love with the tool. But with M they really can turn the magic on.

My next step.

I have not mastered all the challenges yet, so I will go back in time and look at each of them again together with the given answers of the #30DQUERY team mates. Thanks guys it was fun working and learning from you. Stay in contact and stay save.

Live long and prosper.

15-05-2020 The Challenge

Birthday not in the weekend

My route

• Build the filter on the BirthDate column, I used Date.DayOfWeekName first, but that was not folding, so I fell back to Date.DayOfWeek
• Needed to find out what number Saturday (1) and Sunday (7) was.
• Removed other columns
• Put the columns in the requested order.

My result

14-05-2020 The Challenge

Getting in between

My route

This were 3 steps in the Power Query Editor and I was done, so I wondered where the catch was. Looking at earlier solutions I learned the catch was in the notation of the dates. Alex had hope people would start change the datatype of the birthday

• Load the table
• Remove the not needed columns
• Put a filter on the birthdays

My result

13-05-2020 The Challenge

A union to be careful with

My route

I did not fully understand what Alex was asking us to do, so I used the posting from Steven Bitaxi to read his blog and in his first line I found out what needed to be selected from what Entity. I also read, that Power Query does not have a UNION function, so I was wondering if I needed to skip this Day, like I did with the earlier days, where I did not find a folding query solution. Still I started using the toolbar in Power Query.

• For both Tables I removed all the columns that I did not need
• I made sure that both columns in both Tables have the same name, by typing it into one and copying it into the other one.
• I used the option Append Queries and got one column.
• Removed the Duplicates and ended up with the same number of rows as Steven Bitaxi
• Sorted the rows
• I saw there is one row with the ‘name’ Unknown and decided to remove that row

The Native Query is still folding, so I seceded this challenge by using all the build in Power Query options. The difference is, that I have two query’s and that I need to be aware not to load them both into Power BI when I want to make visualisations.

My result

09-05-2020 The Challenge

A leap

My route

• Remove the not needed columns
• List the formula for last date of each month
  Date.EndOfMonth(dateTime as any) as any
  Noop that is not right, that is just giving the last date of each month in each row
• Let’s go for [Invoice Date key] == Date.EndOfMonth
  Yes, I got a filter
  Noop the folding broke
• I leave it with this, I did 3 challenges this day and it has been enough thinking for 1 day

My result

08-05-2020 The Challenge

Future proof

My route

• What is the formula to selectcolums
  Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as table
• In the home Tab, there is an option Choose Columns and I selected all columns that do not contain the tekst ‘Key’ with this option.
  And yes I now see the columns I need.
• I think something should be done with the word Dynamically, because if now 1 row will be added to the original dataset it will not be in this filter.

From Owen Auger

From Barrett Studdard

• So lets start looking in the documentation for Tekst.contains and List.select
  From what I find I cannot bring that together with the used code by the two people.
  Looking at List.select I now see why those two are in combination with each other; List.Select gives a list and Tekst.Contains gives True or False

My result

07-05-2020 The Challenge

Getting even

My route

• Remove not needed columns
• Filter out the N/A from Colour
• I wanted to use the function Number.IsEven for the column Stock Item Key, but it kept giving an error, so I took a look in already given answers and show people where using the function Number.mod, so I used that as well

My result

05-05-2020 The Challenge

Basic Math

My Route

• Remove not needed columns
• Make new column with the Text Length of [Description] (found the formula for this in Microsoft Docs)

I found out that there are 2 different types of formula’s DAX and M and they should be used at different locations in the program. In de Query Editor it is M.

• Make new column with the Modulo
• Mmmm, maybe it is nicer to put the formula into one so it is just going to be one column. I put the code for text.length into the formula for modulo
• Filter the rows so only Modulo 1 is left

My result

04-05-2020 The Challenge

Watch the order of applied steps!

My Route

• Import the two Entities
• Remove not needed columns from Both tables
• Create [TotalUnitPrice]
• Remove [OrderQuantity] and [UnitPrice]
• Give it the Fixed decimal number format => fold broken, so removed again
• Merge DimProduct into FactResellerSales, Inner Join at ProductKey
• Took a peek at the solution from Barrett Studdard, because I do not know how to do the aggregation, learned I need first to group at ProducKey. Now the question, where is this option in my Editor. Ah found it, it is in the Transform Tab
  • First try I get the new column, but the name columns disappear
  • So that is why it needs to be the advance option of Group By
• Last but not least, filter TotalRevenue at one million
• I would like to see it as a Fixed decimal number, but when I do this the folding breaks.

My result

03-05-2020 The Challenge

Filters. Filters. Filters. We’re in for it now.

My Route

• Import the two Entities
• Filter the [ResellerName] and the [CountryRegionCode]
• Join this two Entities using the merge queries
  • DimReseller [GeographyKey] Left Outer join DimGeography [GeographyKey]
• Remove the not needed columns from DimReseller
• Show only the needed columns from [DimGeography]
• No Transformations

My result

Yes today I got a result in the Native Query

It became a table with 241 rows.

This morning when I checked again all the information and some of the other outcomes I realised I had missed the instruction that everything should be done in the Power Query Editor and not to make visualisations.

So, lets go and repeat Day 1

My Route

• Open Power Query Editor
• Import via the option SQL Server the Entity <DimEmployee> op my computer
• Follow the recommended practice
  • Filter the BirthDate at 1974
  • Merge the 3 columns that have name parts into 1 new column [FullName] and here it goes wrong. I use the option Merge Colums in the Power Query Editor and then the option View Native Query greys out.
    I have been looking around on the internet to find a solution, but I cannot find it. So, I stop the challenge of today and wait for an explantion on Day 10 and then will see if I can redo Day 1
• Remove all the not selected columns so only the 4 needed are left.

My Result

The Challenge

A little bit of GUI, a little bit of keyboard. With today’s challenge find the employees with a birthdate in 1974.

My Route

1. Open the Entity <DimEmployee>
2. Make the new column by selecting the 3 columns and choose option merge, give the name <FullName>
3. Close and Apply
4. Go to Report
5. Select the visual Table
6. Select the 4 columns
7. Don’t summarize the EmployeeKey
8. Filter the BirthDate-Year at 1974
9. Sort ascending on BirthDate

My result

Report in progress

The Report is published as it is right now. In the coming day’s it will get more shape. Right now it is a working process. So, what you see is the latest update.