#30DQUERY – 30 Days Query Folding Challenge
My learning with 30 Days Query Folding Challenge – #30DQuery
Recently I was working on improving the refresh time for a large dataset and after spending a few days on this subject, I realized that query folding was not happening. And after googling here and there I came across Alex Powers’ #30DQuery Challenge which kept my interest level up to take on this challenge.
My understanding of Query Folding in simple words
We connect to a data source using a query and import data to Power BI. Post that there will be some transformation required like renaming, removing, filtering, etc. The Power Query mash-up engine in order to improve the performance translates the m language into a language that the data source can understand. Basically, generates a single query statement that combines the retrieval and transformation of data. This concept is called query folding.
How to check query folding is active
In the power query editor, at each applied step, right-click and check “view native query”. It should be active. If “view native query” is active, that means the query is folding up to this step and if inactive query folding may not be happening.
Getting to the challenge
I like the fact that Alex Power has mentioned it in his video . There are many right ways to find a solution, but the wrong way would be when you get view enabled query disabled in any of the transformation steps. This line is so captivating for me to pick up the challenge.
Day 1 Challenge
Solution
This challenge is a very easy one that can be done in a few minutes. I tried it in 2 ways. Option 1 – I wrote a SQL query that does all the transformation and filtration. I was able to view the native query active.
Option 2 – I pulled the dataset and did transformation on merging names, and then filtration to include Birthdate between 01/01/1974 to 12/31/1974.
In both ways, the query folding was active. I prefer transformation and filtration at the backend as much as possible, but since this challenge is to get the transformation done on power bi and enable native query, going forward will do all the transformation and filtration in power query editor
Day 2 Challenge
How to solve this:
- Select the required columns – ResellerKey, BusinessType, ResellerName, NumberEmployees, ProductLine
- Filter rows with Reseller Name that contains ‘Bike’ or ‘Bicycle’
3. Click on the Merge Queries option and join with DimGeography using GeographyKey
4. Right-click on the final step of the merge query and the view native query is enabled.
Day 3 Challenge
Step 1 – Select only the required column
Step 2 – Merge with DimProduct table using product key
Step 3 – Create a custom column for TotalUnitPrice
Step 4 – Click on the group by option and aggregate the TotalUnitPrice based on ProductKey
Step 5 – Filter the TotalAnnualRevenue with an amount greater than 1 million
Step 6 – Right click and check view enabled query is true
Day 4 Challenge
Step 1 – Select required columns
Step 2 – Add the next step to finding out the length of the Description field using Text.Length()
Step 3 – Write the formula to find out the modulo of length divided by 10 as below.
Step 4 – Filter the columns where Modulo by 10 is 1
Step 5 – Right click and check view native query
Day 5 Challenge
Step 1 – Select the required column
Step 2 – Add a custom column to extract the year of the Valid To Date
Step 3 – Create a custom column to find out the duration of days between and valid from and valid to date
Step 4 – create a conditional column as below
Step 5 – Filter the column with valid record flag as true
Step 6 – Right click view enabled query
Day 6 Challenge
Step 1 – Select the required column
Step 2 – Filter out the columns with color <> N/A
Step 2 – First I took even odd information, but query folding was breaking. So took this modulus by 2 approach as below.
Step 3 – Filter out the column and select one where modulus 2 is equal to zero
Step 4 – Right click and check view native query
Day 7 Challenge
Step 1 – Import the required table. After the source step, add a new step to list out all the column names.
Step 2 – Convert the list to table.
Step 3-The objective here is to keep the columns without ‘key’ text. Add a conditional column to differentiate between key text and non key text field.
Step 4 – Convert the custom columns to Number format. Doing so will generate error for non numeric field.
Step 5 – Select Custom Column and right click to remove errors.
Step 6 – Click on the column and select Drill Down. This will convert the table into list.
Step 7 – Add below step to remove the ‘key’ text column.
= Table.RemoveColumns(dbo_DimEmployee,Column1)
Step 8 – Right click on the last step and view native query is enabled.
Day 8 Challenge to be continued….
