Hi everyone, Hotigo here once again for another Power Query video. Today, I would like to share with you how we can use Chat DBT to help us with data modeling in Power Query. So, without further ado, let’s do it together.
To start, I would go to Power BI and open the Power BI application. Once opened, you will see the Chat DBT feature. This feature allows us to combine Chat DBT with Power Query. Okay, let’s get started.
Firstly, I would go to Power BI and open the Power BI application. Once opened, you will see the Chat DBT feature. This feature allows us to combine Chat DBT with Power Query. Okay, let’s get started.
To begin, click on the ‘Get Data’ button and select the ‘Excel’ option. Then, choose the ‘Dryer Sample Data Set’ and click on ‘Load’. Instead of clicking on ‘Load’, click on ‘Transform Data’ as we want to work in Power Query.
In Power Query, you will see the applied steps on the right side of the screen. These steps are related to data modeling. To be more specific, we will add two more steps to the existing three steps.
Next, select the columns ‘Segment’, ‘Country’, ‘Product’, and ‘Unit Sold’. Then, remove the other columns. Now, we have four steps in total.
After that, go to the ‘Country’ column and filter it to include only ‘Canada’, ‘Mexico’, and ‘United States’. Now, we have five steps in total.
Now, let’s move on to the Advanced Editor. Click on the ‘Home’ section and then click on ‘Advanced Editor’. In the Advanced Editor, you will see the M code, which is the language used in Power Query.
M language is a functional programming language used in Microsoft Power Query. It is specifically designed for data transformation tasks and allows users to query, clean, transform, and reshape data from different sources.
In our code, we start by declaring a variable named ‘Source’ and assigning it the value of loading an Excel workbook. Then, we extract the ‘Financials’ table from the loaded workbook.
Next, we apply data type transformations to the columns and select only specific columns from the ‘Financials’ table. We also filter out rows where the country is not equal to ‘France’ or ‘Germany’. The result is a table that is returned as the output.
The M code in Power Query can be written using the regular click-and-drag method or by directly programming in the Advanced Editor. Using Chat DBT, we can get a better understanding of the code and its functionality.
To use Chat DBT, simply copy the code from the Advanced Editor and paste it into the Chat DBT interface. Chat DBT will then provide a detailed explanation of each line of code and its results.
In summary, using Chat DBT in Power Query allows us to enhance our data modeling process. It helps us understand the code better and provides documentation for our queries. If you found this video helpful, please give it a thumbs up and subscribe to our channel. Don’t forget to share this content with others who may find it useful. Thank you for watching!
