Buenos tardes this way, I'm very happy for being here.
Ready for talk about power query about power BI and about some transformation that can save you our ETL in power BI. I want to thank the sequel, Bates Organization for the event itself want to things that sponsor.
First, I want to thank all the volunteers for their help OK. I'm business in Italian can. Sultan Microsoft partner in power. BI Microsoft data platform. VPN lending learning trainer that's all about me.
Going to the to the point we will today. We will talk briefly about the definition of ETL process. In general and ETL processing power BI Desktop in particular.
Power BI ETL process goes through the 3 steps extraction transform women's data quality transformations and other data transformations and then loading data into the beautiful cellular data model OK.
The ETL process is the first and definitely the most important layer of a business intelligence system. It's a hidden layer. The final user who looks like the beautiful's analytic analytics reports, and dashboards.
See all this stuff we made inside our power query editor for obtaining data for transforming cleans in an for preparing all the stuff after loading into the the models ETL processes as?
Implement as necessary for the success of power, BI Project. We could see that power that ETL step is the most important for deployed so that the success of power BI of.
Display it depends on the good or the bad it'll OK. Let's talk about power BI power. We construct data from different sources from files folders database scripts cloud and many others.
This dang transform doing some transformation for data quality filtering rows and columns formatting our data as a table and then.
Combining queries merging queries doing a lot of tax for modeling purposes. Like Pivot on payment failed transpose and many others. And then we need to decide who each of the queries will be allowed to the model anguish not.
We load our data to the model and then we need to fix the model. We need to check the relationships. We need to hide columns and we need to improve some our model, creating decks calculations and so on.
After that, we have all this stuff necessary for creating publishing and sharing report. San dashboard using power. BI desktop power. BI server on power. BI mobile mobile OK. Let's go to.
OK, we have here.
Different sources.
OK, we can hear different sources. Our purpose is to create a star schema tabular model using different sources, combining and performing several tax for 4.
OK, the first one, will be this one sells here. I have here a power. BI desktop file empty total EMT OK. Let's obtain the first file ourselves are storing in a file with?
Air PT we have now saying here for connecting to LPT but arpita file. We can connect using the text connector OK. Let's see here we can see. I'm here in the data source, but we are not able to CRPT.
To select other files ancelet sales for the last year for 2018 OK.
Power be, I will look at the data and here we have our navigator.
Looks.
Looks good OK, let's edit now we are inside the power query editor. We are in the correct place for making all the transformation. We need we need use first roads as heaters who we need to.
Move the top rows the very first rose.
Well, we can't see bad we need to remove also the 3 button rose.
OK, I will write off the change type.
Transformation here because I prefer doing again, OK that's all we have our data, it's clean here we.
Half hour now it's OK, we have all our columns, OK great OK. We have sales. But my boss, said, OK, now. I want to see the information from the other years from 2017 because I want to have all the day.
Just one table OK, let's look.
At my data source here we have not data from 2016. But we have another folder. This file we could connect to hang and do all the transformations again and again bad.
We are not Leslie, where you know, we are not lazy where efficient has said our.
Patrick OK, so we could duplicate that means copy and paste duplicates becaus. The files are really, really very similar. We can duplicate here. We have just for transformation. But imagine that we have 20 or something like this we are able to do.
Then we have here the last year and just to select the color red.
Bath from the our air PT file with data.
That's perfect now all the transformations are down. It's easy. The only things I need to do here is to read off of this for columns. The files were similar, but not identical and now it's necessary to join them in just.
Query here in power query. We have here the possibility of happened queries OK, you could see here app increases new.
I've been queries as new and we will happen. Our both queries that's all in just one query.
Our sales are here, OK that's OK, an now I prefer doing now. I could select all the columns going to transform and detect data type for each of my columns, OK cells is the.
Now we don't need to learn to the model sells for each.
Dear independently is not necessary so we are.
Not enable
The Lord just for sale, OK, let's connect to the other.
Sources our customer has the data about our customers are storage in the SQL server database.
We are connecting.
I will import I will not use a direct query here will import.
I will connect to adventure works sorry one more time DM customer and OK.
OK, we have the columns from being customer, I will change the name OK.
We need to choose which columns we need here, we need customer Kejora Ficino. We need birthday and fine last name, something yearly income for example, but the into.
And here is power BI is able to see that.
Because of the relationship between Jim customer and in geography. We are able to select the columns front in geography without create any specific query here OK. Let's Aladdin Geography, select select OK.
Reading your fee now we have the columns from customer an one columns that contains a table for each rose that contains all the information about Dean Geography, but for each one of our customers. The only thing we need to do.
To expand.
Just for selecting the columns, we need in this case, city and country.
And that's all here we have.
2 columns from geography, add our to our customer table. That's all OK does not total because here we have birthday birthday is very, very bad column for tabular model.
Animal gharrity high granularity very high granularity in this column and and also it not have sense to work with birthday probably we need the age for each person OK for working in this case, it's really easy with.
Cause we have transformation for the table for any columns and then we have transformation for text column number columns and date and time columns here we have from date columns.
An option age, they will returns the days the all of the days this person is alive is ass. Granular E as before, I know, but in this case, we have a type duration and from duration. We are able.
Obtain total years.
So now is better is ass granularity as before, still OK but now we will round.
This data, becaus now we have a number. We will round of course, age round down every time down because of people want to have more ages, OK now it's pretty good.
Data for a tabular model is not too big but normally we need to create several groups of ages orange for this purpose here in power query. We have the possibility of add call.
Using the condition OK create the conditional columns here.
This is OK, let's change the name here, we have the Age.
Now create the conditional columns, conditional columns will be age.
Ranch and then if HS for example, less than or equal to 50.
This person is very young.
Otherwise is young.
Perfect.
OK, now we have 2 values 2, Alesis Perfect, 23 formalises verified for the are perfect for the tabular model POV OK.
You know, I will change here for text OK, you know it. It's good, but the problem is if we change.
At anytime we need to change this number for any other number or suppose that we have several conditions here with different numbers is not very good idea to go to the.
Transformation here and change and change all the time, no for this reason is really useful to work using parameters is very easy to create we could manage para meters create a new one this will be age.
Level for example, LH level one in case we have different conditions OK. So type number and we could put here our 50 Grade 50 and now we need to change.
The value in the condition.
You could say here we can use a value a column or a parameter.
And that's all.
By now if we have person with its 4647 years old. It's very young. But if we change our mind and we said. Oh, no very young just till 45, so this person is not long.
Help is just junk OK. That's also we could use para meters because it's very, very easy to use. It is honest really, really good for the maintenance our code OK. We have sales we have customers.
We are looking to the product our product is in a fall are in a folder. We have 3 files here Excel files in a folder the bunnies. We are not going to do the same as we did before with cells because now we have.
But the idea is that each day we will have 11 more and one more file one for each day so we need to select the folder and analyze the folder itself, so new source.
Folder connect.
Select OK.
In this case, we have a pretty pretty clean folder is not the real life is just because of the time you need to check check check Check all the time the folder because of quality issues here, OK great, so we have 34.
Is here and we will combine files you can see? We did before happened queries and now we will combine files this button here is for combining files.
OK, perfect. The point here is we have 3 files 3 XL fights. This this structure is the same. But the problem is the table for the first one, is P mountain for the second. One is P road for the other is P another thing so.
Not able to use this data here, the secret point here is that if we edit the folder. We can obtain the metadata from the excels and power by now, it's doing the Magic.
It obtaining on all the information from those 3 files here we have 32 objects from each excel files. I will trade off of this.
And also power VI creates.
A parameter.
A value for this parameter the first file.
Hey it found and then.
The place for making all the transformation before combining the files and this is a place. This is a beautiful place be cause by first we need to select just the table.
Just a table not to objects just the table and then we could.
Read off of hidden eat MKE time OK. That's good great now. If we come back to our product. We could see that we have just 3 elements here, not 6 now we have.
Just 3 OK, we have another problem here, we have a requirement for our customer.
That we need the this text.
We need the words is here, OK, we could do using M code. But we are not able to write encode. Unfortunately, bad power. BI is so clever and we can add a column from example in this case from select.
I've selected if I right here.
Mount time.
OK, we could see that power BI wrote create this pression I need OK, so I need the text after delimiter oh it's really.
OK um this is for product line.
Rose line OK.
Great now we will have broad line for Montaine for Rotan for tourism, fantastic now.
We are able to obtain the data itself.
OK, select product key in this case product sub category for example, color and list price that's all.
Here we have all the columns with selected from the first file from the Mount. I'm OK and then we will have all the columns for all the files. We could see here we have mountains. We have roads we have dorians here.
All the columns, we need from all the files is magic OK now we could.
Select the correct transform detect data type OK, the good news here is that if we change.
Our.
Our UM folder for example.
We selected 2 more 5 files and we put here now we will find we will have 5.
Differ in Excel files. They only the only thing we need to do for saving our ETL is re fridge.
That's all now we will have here 5.
Um product lines, depending on the all the values, we have in our files.
Great.
That's great.
Now we need to extract some information about sub categories from and a word file power be. I just not able to connect directly to word file but power BI is able to paste the content that we have at the clipboard.
Copy I'm copy this information from the word, and I'm best in past in here in the in the power query editor using this option here, Interdata OK, so inter data.
Based.
That's all sub categories.
Fantastic.
Great.
That simple, now another challenge. We have because the information about the categories. We the information is storage on a compressed zip file again power queries not able to look at the same file at the compressed file back.
The queries able.
To ruin their our script, so we have our strip here very, very simple are dis able to unzip file and return a data frame.
2 of power BI.
Let's do it now we will copy this code.
Going to our power BI news source.
From all the other sources here and then our street.
Connect just based the code nothing more will be.
No similar will be necessary.
OK, our code it's executing and we obtain the information about.
The categories.
The only thing we need to do is for the quality of the data is split using delimiter power BI realize which is the delimiter.
This is where the ID category an just here category.
OK.
That's all now we have bread loot. We have sub categories and we have categories but the point is Gwynn Meats and stars schema.
If we uhm relat our sales to the product product to the so category subcategory to to category. We will have snowflake schema so that means we need to merge all 3 queries OK. Let's do it, we could merge.
Creating from product to sub categories, OK now we need to select Proto Category Key from here, so category key from from here and then.
Join condition and so on. We are doing something like V. Look up in Excel now. I like I change name likes here. We have product OK now we have all the columns from product table.
We have a column with a table for each row depending on the values for the sub categories and we are in the same position as we were before when we were working with customer and Eurofix.
Expand here category ID for connecting to category. Later, an sub category column because of the need and we could repeat.
For product now to category OK.
And Select Category ID ID cat.
Again, a table that contains the information from the category table.
OK, select category here and that's fine, so in just one query. We have the information about customer. Sorry about product about Subcut. Sub categories, Ann about categories. That's fine. I will change this name here.
OK and we need to do some transformation here for cleansing our data by first. We don't need source name and name and we don't need product subcategory key and we don't need category ID.
OK.
We need to apply to our model as less.
Ass possible great now we will.
A blood this query product query. That means that we don't need to load.
The product the original products we don't need to lower the sub categories. We don't need to lower this query too.
By now we have sales we have customers and we have products.
OK, just 3 queries that we need to love to the mother OK. We have another another source. Here is an XL file that contains information about promotion sales. The point here is this an Excel file. Let's connect to the.
Excel file.
But we have a challenge here because here we have the information.
Um promotion OK here we have the information.
About self a lot of columns here with cell information.
For any reason for any reason we are not able to decrease the granularity for this table. We are not able to delete any columns because of the some business requirement. This query will be useful for any other.
Is so?
If we have a query than we are not able to change the transformation here that can say for ETL is to reference so we will maintain the first one, and we will ref.
GS 4 day promotions.
OK.
Now, now we have another query. We are able to do any transformation. We need for example, promotion key English promotion name promotion. Taipan promotion category that's all that we need great of course we?
Seeing that a lot of repeated values here, we have here a transformation for cleansing data remove duplicates.
That's all this is our data. We will applaud that means this one query will not be necessary to overload OK. Let's change the earth can you see a lot of transformation OK? Let's
Let's apply all the change here.
OK and now let's look at list half a look at life demo OK.
Well, beautiful star.
Schema tabular model bad we have nothing about calendar date. We need our calendar table here OK is there is a lot of way to do this bat.
Just for showing you here we have.
A great and beautiful script from the Master of the universe and encode Chris Webb and we could use it because greet publish.
He's really, really.
Generals so we just can copy this code this is great and paste into?
Our power query how we are doing so, we, we will create a new source OK.
Other kind of data and we will use blank query.
Connect.
For blank query. It looks like it's blank, but is not blank as soon as we go through the advanced editor.
There is some content we read off and just paste the?
Query.
OK, this is because of the copy and paste that's all OK here is the code.
Donde and now we have a function writing by Greece Web for creating a calendar table.
The only thing we need to do is to define the value is for the first for the start date and for the?
And 8 OK, let's do it.
Great and now just invoke the function with those parameters.
And that's all.
Fantastic the only thing we need to do is to.
Select transformation detect data type it's really, really important to.
Do this transformation just for selecting data type correct for each one now this is our calendar.
Calendar table, OK, we could've lot, that I will apply.
Let's let's.
Going here well, let's going here and let's.
Fix the relationship here between 2 tables and that's all now we really have our schema. Our star Schema Tabular Model Totaly prepare oh sorry.
I want you to I want you to show you.
They like Dick structure all the steps we did. We could see here in the view tab. We have query dependencies?
If we look at the query dependencies we will see.
That for our model, we have here a lot of things for example, if we are looking at product, we could see that.
We have
Here.
We have here our folder.
Sample file created by power BI.
Parameter created an function created by power BI and then products. The query products, then we have also sub categories here just we copy and paste into the power BI and then categories.
Our code you could say our code categories. An categories subcategories on product makes our product OK. That's that's fine and we can see that load is disabled for any of those, 3 and just enable for the.
Here we have.
Well, the sample files also this one, an for sales 2 files at the beginning 2 files to origins, then 2 queries independent here, we have now any link between those.
Or together with the union and append queries using those 2, here we have customer inside the customer. We have information about geography. We needed, we don't need the 2 queries here because of the sequel.
Yeah, origin. Becaus power query realized that this relationship between both and we have a para meter here for the age range, OK, then we have here the information the calendar table from?
Function from Chris Webb function and for promotion. We have the original file. Then the original query and then the other query that we reference so here we have a link.
So we looking at the query dependencies. We are able to know a lot from our ETL process.
OK, here, we could create several different improvements.
But I prefer just to open the final version here because we can grouped the queries. We can group the functions. We can create comments. We can improve the labels for all the apply change.
For the apply step and so on in in this case for example, is the same. But just with more time and here we have.
OK, the the information for our model, the para meters here. The power by combining functions and all the queries. We have in our ETL so.
Come back to the queries to the slice.
And.
Here.
We extract.
Our data front 2 air PT files.
Then we extract from folder products files for sales for a product from folder remember 3 files and then 2, more from clipboard from sub categories from word file.
Dan are script for categories, OK, then Excel files for promotions and then SQL server tables for customer and geography. All together and blank query for data for calendar.
From Chris Web OK. That means with threat information for several data source different and then we transfer. We did several transformation just for obtaining a good data model A good day to model we need.
Create a good tabular data model and we obtain the queries for the model. The additional queries. There also important. But we we are not to loading them and some parameters some some functions and.
OK and we have here our beautiful beautiful star schema solar model that's all Corey editor is empowered by it's really, really powerful tool for ETL purposes data could be extracted from.
Many different sources transform for different format to a model for clean scene for improving. The model taking in account. The tabular model that we need an load into the tabular model and that's all happy querying.
That's only.