Power BI Desktop – Group By, Big Data and DAX Studio

00:00:00 welcome back in this video we will not
00:00:04 create a project in power of your
00:00:06 desktop we will work on big data this
00:00:08 time well big data in our case is
00:00:11 actually just a single CSV file with two
00:00:14 columns but each column has more than 77
00:00:17 million rows and by that we have a total
00:00:20 file size of 1.8 gigabytes which is
00:00:23 quite big actually so we will connect
00:00:25 part of your desktop to this source file
00:00:27 then aggregate the data so reduce the
00:00:30 roles basically and finally export that
00:00:33 data as a CSV file so create a new
00:00:36 source file actually now before we start
00:00:39 one quick note I'm aware that normally
00:00:43 you don't do this in power of your
00:00:44 desktop because there are meta tools
00:00:46 available on the market for purposes
00:00:48 like that nevertheless in case you only
00:00:51 can use probably a desktop and you have
00:00:54 to do things like that I just want to
00:00:56 show you that it is possible now having
00:00:58 said that let's start
00:01:03 so this is the new power bi desktop file
00:01:06 I created and they're already connected
00:01:08 to free different source files free
00:01:11 source files I talked about one right
00:01:13 yes but we have data for the New York
00:01:17 City yellow caps in here so in the first
00:01:19 query you can see the data for the
00:01:22 second half of 2014 so we have each day
00:01:25 of the second half we have to sum up the
00:01:28 rights of that day and we have to sum of
00:01:30 the fare paid at that day so we have the
00:01:33 same thing for the first half of 2015
00:01:36 but for the rights only as you can see
00:01:39 right here so this is the second query
00:01:42 that we have another third Kure this is
00:01:45 the one I talked about in the beginning
00:01:46 and this is that big source file and as
00:01:49 you can see we don't have daily data in
00:01:51 here well we have daily data right but
00:01:54 data for each day and each single right
00:01:58 that took place so we have as I said
00:02:00 more than 77 million rows in here and
00:02:04 what we want to do is we want to
00:02:06 aggregate that information to daily data
00:02:09 only and to do this we need a function
00:02:12 in power bi desktop but before we start
00:02:15 let's first format that first column
00:02:18 differently because we only need the
00:02:20 actual date and not the time so to
00:02:23 change this we simply go to the
00:02:25 transform ribbon right here now go to
00:02:28 data type right here and select date now
00:02:32 its power bi already changed the format
00:02:35 when we connected it to the source file
00:02:37 we shouldn't press replace current right
00:02:39 here but add new step so that new
00:02:41 formatting right now if we do this we
00:02:44 can see that we now only have the dates
00:02:47 in that first column but why was that
00:02:49 important well as I said I want to
00:02:53 aggregate the data and to do this we
00:02:56 again go to the transformer band right
00:02:58 here and now select the group by
00:03:00 function right there and this now allows
00:03:04 us to group and by that to aggregate the
00:03:07 data depending on a column that we
00:03:10 select in our case we simply select the
00:03:13 first column so the pickup date time
00:03:15 column right here where we know if that
00:03:17 daily data actually and now each time
00:03:20 power bi finds an equal date in that
00:03:23 column it would simply aggregate that
00:03:25 now this is good but we shouldn't forget
00:03:28 about the second column that fair amount
00:03:30 right here because this should also be
00:03:33 aggregated to each single day but we
00:03:36 have to specify how exactly this
00:03:38 aggregation or that grouping should be
00:03:41 done to do this we simply can go to that
00:03:44 part right here first define a new
00:03:47 column name which would be fair in our
00:03:49 case like this and now the operation
00:03:52 shouldn't be counter OHS it should be
00:03:54 the sum so that we retrieved the sum of
00:03:58 the total fare paid at the corresponding
00:04:00 day so we select sum right here and now
00:04:04 we have to change the column from the
00:04:05 daytime to the fair amount of course and
00:04:09 this is it if we now press ok this is
00:04:13 going to take a while of course because
00:04:15 power bi will now scan through the
00:04:17 entire source file with that one point 8
00:04:20 gigabytes so we'll see each other as
00:04:23 soon as it's finished and this is it
00:04:26 well to be honest this took quite a
00:04:28 while but the great thing is that we

00:04:31 know if the data the way we want it to
00:04:33 be so we now have daily data in that
00:04:36 first column you can see that and in the
00:04:38 second column we have two some of the
00:04:40 fares of the corresponding days so this
00:04:43 is really good but there is one thing
00:04:45 that is not so good right here power bi
00:04:48 is still connected to that initial
00:04:50 source file so that 1.8 gigabyte file so
00:04:53 whenever it scans through the tile again
00:04:55 it will take a long time so we should
00:04:59 avoid that and to do this we will now
00:05:02 simply load this data we have right here
00:05:04 so this queries with that clean tables
00:05:06 into our actual data model and then
00:05:10 export the data out of the data model
00:05:12 into a new CSV file which will then
00:05:15 become our new source file now let's
00:05:18 load the data into the model first by
00:05:20 going to the home ribbon right here and
00:05:23 pressing close and apply like this
00:05:28 again this will take a few minutes so
00:05:31 see you again when this is done and now
00:05:36 this is done and the great thing now is
00:05:38 that if we change to the data view right
00:05:40 here and open our 2015 fares file right
00:05:45 there then you can see that the format
00:05:48 is no good of course but this is
00:05:49 something we don't have to take care
00:05:51 about right now but you can see that we
00:05:53 only have that 181 rows in here in the
00:05:57 data model as you can see right there so
00:06:00 what we can do right now is we can
00:06:02 export the data if the problem is that
00:06:05 we cannot do it with part of your
00:06:07 desktop the good thing is there is a
00:06:09 separate tool to do this let's take a
00:06:12 look at that tool and this tool is
00:06:15 called back studio you can find it right
00:06:17 here and deck studio simply will allow
00:06:20 us to query data out of power bi so out
00:06:24 of the data model that we just saw and
00:06:26 this means we can retrieve the data from
00:06:29 the table that we just created so if we
00:06:32 now press download right here and then
00:06:35 open back studio right there then we can
00:06:39 see that we have that connect window
00:06:41 that opens in that connect window we can
00:06:44 simply select the second option right
00:06:46 here and here we can see that we now
00:06:49 have our power bi file so this group
00:06:51 file let's select that and press connect
00:06:54 and now you can already see right here
00:06:58 that we have our free tables in there
00:07:00 and these are the data of the actual
00:07:03 data model not of the queries or of the
00:07:05 initial source file that's important to
00:07:07 know if we want to get that data now we
00:07:11 simply go to this window right evaluate
00:07:14 like this so we can select it right here
00:07:16 with the arrow keys on a keyboard press
00:07:19 tab now space well and now simply type
00:07:23 taxi and we can already see that we can
00:07:27 now select our fare data right here and
00:07:29 press tab again and if we now press f5
00:07:33 on the keyboard like this then it will
00:07:36 query our data and you can see again
00:07:39 that it Kure the data of the data model
00:07:42 as I just said now we have the data in
00:07:46 here but we want to export it as a CSV
00:07:49 to do this we simply click output over
00:07:52 here and select file like that now we
00:07:58 need to press f5 again to run the query
00:08:00 like this and as you can see we can now
00:08:04 define a name for that new file let's
00:08:06 maybe call it 2015 new just to know that
00:08:10 this is the new CSV file and now change
00:08:12 the text format right here to the CSV
00:08:15 format right there like this and here
00:08:18 you can see our other input files by the
00:08:19 way so we are in the right folder and
00:08:21 now we simply press save like this and
00:08:26 we now save the file so if we now go
00:08:29 back to power bi desktop so here I am in
00:08:32 the data model and open the query editor
00:08:34 like this and now go to our data sources
00:08:39 right here select as you see file so
00:08:43 here I am now again in that source
00:08:45 folder and you can see our 2015 new file
00:08:48 that we just created so let's select
00:08:51 that and press open and this is fine so
00:08:55 we can press ok right here and as you
00:08:58 can see if we now simply change the
00:09:01 format again of the first column by
00:09:03 going through transform ribbon go to
00:09:04 data type and change it to date and add
00:09:07 the new step then we see that we again
00:09:11 have our 181 rows but now with that
00:09:14 connections to that cleaned and small
00:09:17 source file and this will make the work

00:09:19 in our Creator a lot easier right now
00:09:21 and this is it actually this is what I
00:09:24 wanted to show you and as I said in the
00:09:27 beginning power bi desktop is probably
00:09:30 not the best tool to work with such huge
00:09:32 source data however depending on the
00:09:35 performance of the computer you can do
00:09:37 things like that if you want to do it
00:09:39 and in combination with tech studio you
00:09:42 can also easily extract that new
00:09:44 information and by that create cleaned
00:09:47 and smaller source files so this is it
00:09:50 thanks for watching and see you in the
00:09:52 next videos bye bye
00:09:57 maybe some documentaries which would be
00:10:00 some something for us like furs in the
00:10:02 nature yes jong-il feeling yeah

You may also like