Expert Techniques in Time Intelligence and Web Data Enrichment

expertiseexcelerate

Expert Techniques in Time Intelligence and Web Data Enrichment  Welcome to our comprehensive Power BI training series! This playlist is your ultimate resource for mastering advanced Power BI techniques, starting with our first course, "Expert Techniques in Time Intelligence and Web Data Enrichment." Whether you're a data analyst, business intelligence professional, or a software enthusiast, this playlist is designed to provide you with the latest strategies and best practices for effective data analysis and enrichment.  What You'll Learn: • Advanced time intelligence techniques for precise data analysis • Methods for enriching your data with web-sourced information • Tips for optimizing and enhancing your Power BI reports • Practical examples and hands-on exercises for real-world application  Why This Playlist? • Comprehensive Content: Each course is meticulously crafted to cover all aspects of time intelligence and web data enrichment in Power BI. • Up-to-Date Training: Stay ahead with training that includes the latest features and updates in Power BI. • Expert Guidance: Learn from seasoned professionals with extensive industry experience. • Interactive Learning: Engage with practical examples and follow along with hands-on exercises.   Subscribe now and join our community of learners who are advancing their data skills with Power BI. Don't forget to hit the bell icon to get notified about new videos and updates. Let's dive into the world of advanced data techniques together!  Power BI time intelligence tips Advanced Power BI data enrichment Mastering time intelligence in Power BI Web scraping for Power BI analysis Power BI DAX functions tutorial Enhancing Power BI reports with web data Time intelligence best practices in Power BI Power BI data modeling techniques Web data integration in Power BI Expert Power BI dashboard design  #powerbi  #TimeIntelligence #WebDataEnrichment #BusinessIntelligence #PowerBITutorials #LearnPowerBI

Transcript

00:00Hey friends, welcome back. Now, in this video, I'd like to add one additional dimension table,

00:11which is the dates table. So if you have taken my Power BI master class, beginners to advance,

00:18you know that for each proper data model, we need a proper date table. So that is why

00:24I provide in the resource section, a dates table, which is in M. So which you can find

00:30in the resource section here, it's called dates table code. And this is the one we're

00:34going to use. And the great thing here is that you can use this code also for all your

00:39future projects as well. So there's no need to learn what this function actually does.

00:45But if you want to dive deeper into M, feel free to do that. For me now, let me just open

00:49it. So let me just give me a second. I will open this in my editor. Here we go. And there

00:56is the code as you can see here. So it's an M function, which you can use. So control

01:01A to copy it, select it, control C to copy it. And then we go inside our Power Query

01:08editor, click on no source. And then we choose the blackberry and just paste the code inside.

01:13So go to advanced editor and remove the template in here and then just paste it. So you should

01:21see no syntax errors. So then you are good to go. And then we can click on done. And

01:27this allows us here to enter our start date, the end date. And in case we have a different

01:33financial year, so not starting from January, then we could type in the month number here

01:38as well. In this case, it's just one because the financial year here is January. It's nothing

01:43special here. So for the start dates, I'm starting with the 1st of January in 20, in

01:49this case, 13. Remember our data set hotels contains older data. It's from 13 to 18. So

01:56we get started here. And our last date is I think the 31st of 18. So 12th of December

02:05in 2018. And of course, even though we specified here, we still have the option later on to

02:11adjust the dates table. So for the holidays, if you have a separate holiday calendar, you

02:17could choose the column from this table on the table. In this case, we don't have that.

02:22So we leave it unspecified. And then all we need to do is simply invoke the function.

02:27So I click on invoke. And this allows us here to create the dates table, which is here.

02:33And that's what I meant. If you want to adjust this, all you would need to do is simply you

02:37can go in here in the ribbon bar and just change it to a different date or month or

02:42day if you want. It can be easily changed here and modified anytime. So let's rename

02:47this as our dates table. So I'll go in here under properties and call this dates TBL.

02:53Press enter and we got our dates table with a lot of additional columns. If you take a

02:58closer look, you can see here day of the month, we get information about the weekday, even

03:03though this is currently in German because of my regional settings, but you should see

03:07it in your native language. And then we can also see here the ISO year and all this information

03:12and everything of this can be used to slice and dice our data later on. So just in case

03:18you do not want all these columns, of course, you can always delete the columns you don't

03:22need. For instance, select one, hold control key and select another one or hold shift key

03:26to select some of them and then right click. And you can always delete, remove the columns

03:31if you want to do that. For me, that is fine. I just want to keep everything here. So I

03:36leave that unchecked. And then I just drag my dates table here up to my dimension tables

03:43because it's also a dimension table, which I'm going to use in my model. The query itself

03:48here remains in the other queries, that is fine. And so we are good to go with this specific

03:54dimension. The last one I want to add here is data enrichment. And this is about currencies.

04:02So just for the sake of this example, let's say we have data from or receive data here

04:08from our customer. And let's say the customer here who provides us the hotel data and who

04:14wants us to analyze the data and prepare it for Power BI is located in, let's say, in

04:20Europe somewhere. Now, this means that in this case, currently the value here, which

04:25are the sales numbers here for various P&L item or cost numbers, depending on the item

04:30itself are currently in Euro. Okay, that's the local currency for that. And let's just

04:37say the client wants us to analyze this in US dollars. So for that, we need an exchange

04:43rate. So normally in companies, often they have also a separate department where they

04:48specify what kind of exchange rates should be used. But in our example, we want to learn

04:53how we can enrich our data using this case exchange rate data, which is publicly available

05:00on the web. And most often, each of the central banks has its own, well, exchange rate table,

05:08which is provided for free. Or you could also refer to an exchange rate API from another

05:14vendor. But most often you have to at least register yourself with a free API key you

05:20can get them. Or sometimes you always have also have to pay depending on what kind of

05:24provider you use there. But for us, we use something which is publicly available. I think

05:30that's the easiest way to do it. And of course, then we'll make sure that everyone who is

05:34following this course can also access that. So that is why I chose this website here,

05:40which is from the ECB, the European exchange rate. And I can provide you this link also

05:46in the resource section. And what you should see then, if I make this a little bit smaller

05:50here and drag it up, you can see that this is the data coming in this case from the 29

05:55of May in 2023. And there I can see different kinds of exchange rates in this table. Here,

06:01for instance, this is always the Euro compared to different currencies. For instance, one

06:09Euro would be 1.0715 US dollars at this moment in time. Of course, this will change when

06:15you watch the video because exchange rates fluctuate. But that's the case right now for

06:19ECB. And you can see also this here available for other currencies. So in our example, we

06:25have Euros and we want to convert it into US dollars. That means our goal is to extract

06:31here this spot rate. This is the rate we're going to use. And then we need to multiply

06:36the values we have in our data set times this 1.07 in order to derive the US dollar amount.

06:44So to do this, we need to extract somehow this table and get this into Power BI. So

06:49let me just select the URL. I control C to copy this. And then let me just remove this.

06:55And let's go back to our here, our data modeling part. And I will choose here a new source.

07:02And this time the source is a web source. So you can click on web here, select this.

07:08And now we need to paste it URL. So I just control V to paste this. And now let me just

07:15click OK. And we should see a window opening. Here we go, the navigator. And this now allows

07:23us to see the table. So the HTML table, which Power Query in this case has extracted. So

07:29under the hood, what happens is that Power Query is analyzing the specific web page and

07:35is searching for table structures. So it's analyzing the plain HTML code and see what

07:40kind of tables are there. And you see that this table is adjusted to table one. And if

07:45I select it to see a preview, we can see in here that looks actually good. There's a currency.

07:52There's a currency underscore one, which is actually the real me. And we can also see

07:56the spot rate. And if you take a closer look, that's actually exactly what we can see here,

08:01right? The currency, this is the currency underscore one, which Power BI identified.

08:07And that is the spot rate, which we need. And the errors, of course, this is not real

08:10data, but this also not what we need. We need these values here. So that seems to be right.

08:18So let's actually select the table as we do here. And then we can click OK to load it

08:24into a Power Query editor. And here we go. Now I can see I got the currency. I got the

08:31correct name here as well. And I also got the spot rate. But I got a little issue here.

08:39The problem for me is the following. The spot rate, and I can show this to you, the spot

08:44rate here is in with a dot as it says decimal separator. This, if you are located in US,

08:52you should actually see the correct numbers. But for me, because in my native language,

08:58the decimal separator is a comma. That's why I can see that these numbers are wrong.

09:04So there should be actually one dot, 0715. But actually, currently, it states 10,750.

09:14So obviously, something is wrong here. And that has to do with this automatically change

09:18type step, which was applied. So as I said, if you are located in US, you should actually

09:23see the correct number and everything should be fine. You should see the decimal symbol.

09:28But if not, and you have the issue like me, then I'm going to show you what we can do.

09:33We can go back one step to promote headers up there. And there, as you see there, the

09:38number is still correct, because here it's, it's treated as text. So what we can do is

09:44leveraging another great feature, which is available in Power BI, which is if you click

09:49on the ABC to change the data type, you can see there's an option using local. And this

09:55is what I want to use here. I go using local here. And yes, I would like to insert the

10:01step because I actually get rid of the change type. And I do it myself. And I say I would

10:06like to convert this into a decimal number. Or here, fixed decimal, I'm using the normal

10:12decimal number. So let me just go with decimal number. And then my local here is in this

10:18case US. I'm choosing US because I know in the US, the dot is used as a decimal separator.

10:24If you have another country, you could also try another country. But I know that in US,

10:29that's the case. So I can search for English and then US here, United States, English,

10:34that's what I choose. And now this is treated as a US text. So in this case number, and

10:41if I click now on OK, you see that now I get the correct numbers in here. So for me, it's

10:45now one comma, which is the decimal separator for me, 0715. So as I said, maybe you don't

10:54have to do this. But still, I think it's very valuable to know that because maybe in the

10:59future you need to deal with, let's say, European data where you have a comma as a decimal separator

11:05and you're located in US or somewhere else where you have normally a dot as a decimal

11:10separator. And then you can do it exactly the same way, but just choose then, for instance,

11:15Germany and another country which has a comma separator and convert it using this, using

11:20the local option. So we got the correct spot rates now, and that's why I need to actually

11:25get rid of that. So this step I don't need. And instead, I will see these data types are

11:31correct. This is text, this is text, that's fine. And this is something I don't need,

11:36so I can just remove it completely. So now I have my currency conversion table here as

11:41well. So let's say, right click here and rename this and say, that's my currency conversion.

11:48That's the correct conversion TBR, right? Now, all I need to do is either could convert

11:58it also or drag it actually up if I want to keep this complete table. But in our example,

12:05I actually only need the first entry, the US dollar. So if you want to try out this

12:10with a different currency, you can do that as well. But for me now, I stick to only one

12:14dollar, one currency, one conversion, which means European euro I have here, and I want

12:22to convert it into US dollar. So for that, I just need to map somehow this table here

12:28to my hotels table. And actually, I only need to map this specific value here, this 1.07.

12:36And I can do that by simply going to my hotels table in here, and just add a dummy column

12:41here. Because for the dummy column, all I need is a common field. So we can merge the

12:46two tables. And the dummy field could simply be US dollar. Because if you take a closer

12:52look, I just need USD as a field. So I can map USD, which would be then this 1.0715.

13:02So to do that, we can go to the hotels table. Then we can simply add our new column. So

13:08we can go in here, add column. And then we just specify here a custom column like that.

13:14And then for this custom column, it's just let's say that's the currency, currency. Let's

13:19just remove this for now. And then for the formula, we simply type in as a string, USD,

13:25US dollar. So then I can click OK. And now you see we have just one new column, change

13:32the data type to text. So make sure that's the correct data type. And now we have here

13:36USD. It's just really a constant value USD for all rows. And now this allows us this

13:44helper column here allows us to go to home, then go to merge queries. And now we merge

13:50simply here regarding the currency here, our currency table. So this one here, and we also

13:57select the currency. And for the privacy levels, this is this message appears now because we

14:04have public data from the web. And here locally, we have private data, in this case, our hotels.

14:11But in this case, it's totally fine to map this together. So we can simply say in this

14:15case, ignore the privacy levels and click on save. It's just if you are in a company

14:20environment, you want to make sure that you really want to, well, add these data together

14:26because this needs to interact with the web data. And you need to be sure that you really

14:30want to do this. But for us now, click on save. And then we should see actually a match

14:36for all the rows, which is true. And now if you click OK, we got our table. And now I

14:43can simply expand this and just say, I only need actually the spot rate, which is conversion

14:48rate. So I'll take these two things and just keep the spot rate. Click OK. And now we can

14:54see this 1.0715 everywhere. It's just the same value. But this now is our conversion

15:00rate. And now I can also get rid of this currency that was just for helping me out. Let's remove

15:06this. And now we have our spot rate and we have the value. And everything we need to

15:13do now in order to get these euros into US dollars is multiply those two columns. So

15:20let's say, go in here, go to add column, use a custom column. And this custom column is

15:28simply, let's say, the value converted. I call it converted. So I can keep both of them.

15:35But in here, I could simply say now I want to use the value column and I want to multiply

15:40the value column. So type in a star and I'm using the spot rate. You can see that there

15:47are no syntax errors. So yeah, hopefully you can see that. So we're good to go. And then

15:52we can click OK. And this gives us one column in here, which is this one. And this is simply

15:58the multiplication of this value here on value column times spot rate. This one here. And

16:04this gives us now our converted value, which of course we want to make sure that this is

16:08the correct data type. So I can go in here and just convert this, go in here and say

16:14decimal number. OK, so we are good to go. And then we can actually right click here

16:23and we can disable the load because we also don't want to load this in the model, but

16:27also include in refresh. Now, the main benefits to do it this way using public data from the

16:35web is, of course, that this also changes if we refresh the data model. So that means

16:41we will always, whenever we refresh, we get the latest data in this case from this website.

16:48And that's why it's not static, but it will update each day. Of course, if you want to

16:53make this static, you could also do this by simply adding this as a constant value instead

16:58of referring to the current conversion or simply right click here and say you don't

17:03want to actually include it in report fresh, then this data will not get updated. Right?

17:08Then you have a certain point in time where you basically lock the exchange rate. If that's

17:12a use case for you, you could do that. But by default, as long as we include it in refresh,

17:18then also this data gets refreshed and then we have a new exchange rate and of course

17:21those values will change. So this is that for that. And now we are actually done creating

17:30our dimension tables. We have our fact table, we have values in this case, the relationships,

17:37which are the ID columns, which allow us to map the manager table for instance, and the

17:41other ones with the index we have created to the hotels table. So there we have an index

17:47or via the hotel ID or via location ID and manager ID. So this allows us to map the tables

17:54we have here and the dates table can be mapped of course with the date column here.

18:00So now the final thing we need, which we need to do to set up the data model is of

18:03course at first go to home and click on close and apply to apply all the changes we have

18:09done so far. So let's do that. Let's click here. Okay. And now the data gets loaded and

18:17now we have our tables in here. All right. So let me just apply changes and also then

18:24go with that. Sometimes I have this issue here. What helps to get rid of this, this

18:29little hint here is always to save this, close it, then save the model. Press the save

18:34button here. Yes, apply it. And if I save it and I reopen it, then this message will

18:39be gone. And I'm going to show you this in the next video. So that's it for this video.

18:44Hopefully that was helpful. And in the next video, we make sure that we first set up the

18:49model relationships, which is crucial to get started with visualizing Power BI. And afterwards

18:55we can actually start diving into the data. So thanks a lot for watching and for participating.

19:01If you've got questions, feel free to ask. Otherwise, I'll see you in the next video.

19:05Until then, best guys.

Category

Transcript

Recommended