Adding Excel Data to an Existing Power BI Model

Locked lesson.

Upgrade

About this lesson

In this module we will explore collecting Budget data to link into our dashboards.  The only issue is that it comes from a completley different source: an Excel file.

Exercise files

Download this lesson’s related exercise files. You can download the source data files for the course from the resources section of your Lessons page.

Collecting More Data.pbix
348.9 KB Collecting More Data - Completed.pbix
369.7 KB

Quick reference

Adding Excel Data to an Existing Power BI Model

Working with Excel data in Power BI.

When to use

When you need to add new data into your Power BI solution that lives in an Excel file.

Instructions

There are a great many different connectors that you can get to via the Get Data button.  As there are too many to focus on, we will look at some useful techniques for cleaning data here.


Generate headers if required:

  • Databases and tables will have this done already, other sources may not
  • Use the Remove Top Rows command to get rid of unnecessary header rows
  • Promote a row to headers
  • Rename columns that don’t make sense
  • Filter out any data that you can

Filling into gaps:

  • Use the Fill Up or Fill Down commands to fill data into areas that contain null values
  • If you need to fill across rows, go to Transform -> Transpose first, then fill, then Transpose the data back

UnPivoting Data:

  • Select the column(s) that you wish to keep
  • Right click one of the selected column headers --> UnPivot Other Columns
  • Review the attribute column and filter out anything that shouldn’t be there
  • Rename the Attribute and Value columns appropriately

Finalize the query:

  • Set your final data types
  • Rename your query

Hints & tips

  • If the column headers are dates, DO NOT SET THE DATA TYPES BEFORE UNPIVOTING.  Doing so hard codes the dates into the code, meaning that your query will break in future
Login to download
  • 00:05 Now it's great that we have one table of data.
  • 00:07 And we can build some visuals out of this.
  • 00:09 But the reality is it would be much more interesting if we could add some more data
  • 00:13 in to enrich this particular scenario.
  • 00:15 Something logical here would be to add budget so
  • 00:18 that we could compare it to our sales.
  • 00:21 Now a logical place to get that information where most
  • 00:24 accountants actually do their budgets is in Excel.
  • 00:26 And that's what we're gonna do but I just wanna take a quick sneak peek into this
  • 00:30 Excel file because you're gonna see that this particular one has some challenges.
  • 00:35 This file is not in a nice,
  • 00:36 straight tabular format that we need that has the category or location and
  • 00:40 the category and the date and the amount repeating all the way down it.
  • 00:44 It's in what we call a pivoted format.
  • 00:46 You'll notice that there are two years worth of data that are here,
  • 00:49 across the way.
  • 00:50 We need to bring this into Power BI and get it into a nice table.
  • 00:54 And that's exactly what we're gonna do.
  • 00:56 So what I'm gonna do, is I'm gonna go and say let's get data.
  • 01:02 We're gonna grab data from Excel and
  • 01:04 we're gonna browse to get the Loaded Pencil Budgets file.
  • 01:07 And we'll say okay.
  • 01:11 It then gives us a preview we can click on sheet one
  • 01:14 you will notice that here is a preview of the data.
  • 01:16 I'm gonna check the box because I want to bring in that worksheet and then I'm gonna
  • 01:20 say edit because it is really important to edit this before it gets brought in and
  • 01:24 it opens helpfully again behind the window I need so here we are.
  • 01:29 Now we have some really cool tools to work with here.
  • 01:33 The first thing I'm gonna do is, I'm gonna set this up and
  • 01:35 I'm gonna make this called budget as my table because that makes sense.
  • 01:40 Now what I'm gonna look at next is I'm gonna try and figure out what to do here.
  • 01:44 You'll notice that I've got three rows that have nulls all the way down.
  • 01:47 So I'm gonna say let's go remove the top rows, okay get rid of the top three and
  • 01:52 then I have my column headers that are moving all the way across with my dates.
  • 01:56 Those are gonna be important so
  • 01:58 what I'm gonna do is I'm going to go in use the first row's headers but
  • 02:02 I'm not really thrilled with what I've got from my first two budgeted sales and
  • 02:07 column here so this one here I'm going to rename to location and
  • 02:11 I'm gonna rename my column two to category.
  • 02:14 The next thing that I'm looking at here is that I can see that I've got my
  • 02:19 location for ethical development and it says null null null null null.
  • 02:24 Well I really need this data aligned on each row with the individual categories.
  • 02:29 And then with tax evader location I need his name going down here as well.
  • 02:33 So, we have this really cool tool here that we can grab.
  • 02:36 It's on the transform tab and it's under fill or we can right click on
  • 02:41 the column and we can also choose to fill and we can fill down.
  • 02:44 And what the fill command does it fills into areas that are null and
  • 02:49 repeats the use records all the way down until it finds something like ethical
  • 02:53 development total that has a value then it will stop and it wont replace that.
  • 02:57 Key thing to remember about the fill command is it only fills into areas that
  • 03:00 are null.
  • 03:01 It will not fill into areas that are blank.
  • 03:03 Okay, those are different.
  • 03:04 So you'd have to replace blank or nothing with null in order to make that work.
  • 03:10 Now the next thing is I don't want anything that has totals in it.
  • 03:13 How do I get those out?
  • 03:14 I could filter here and say,
  • 03:17 let's use a text filter for does not contain or does not end with.
  • 03:21 But I've got a better idea.
  • 03:23 Anywhere that I see the word null in a category field is either a total
  • 03:27 or a header line.
  • 03:28 So I'm going to go and filter to say remove the null values.
  • 03:33 And now we've got a nice listing here that has the location and category and
  • 03:37 then we have all of these individual dates.
  • 03:40 And this is where this tool becomes super, super useful.
  • 03:43 I'm gonna grab location, hold down my control key, and grab category.
  • 03:48 And now what I'm gonna do is I'm gonna right click and
  • 03:50 I'm gonna choose to unpivot the other columns.
  • 03:54 No matter how many columns are after this data set,
  • 03:56 it will always pick them up and there we go.
  • 03:59 We have now unpivoted the table just like that.
  • 04:01 This is amazing, and by the way, this works in PowerPoint and
  • 04:04 Excel as well if you're working with that software too.
  • 04:07 Now, we still have some challenges.
  • 04:09 We've got this attribute column here, which is called date, and
  • 04:13 we've got our value, which I'll call amount.
  • 04:18 The data types are not correct on this.
  • 04:21 But I also see in here that I've got something like a total 2015 and
  • 04:25 a total 2016, I need to get rid of those.
  • 04:27 So here's a nice little trick with this again, I'm gonna go and
  • 04:30 change the data type on this to a Date.
  • 04:33 Because this came from Excel and because this is using a proper date serial number,
  • 04:37 I know the format here is always going to be showing based on my Window's settings,
  • 04:41 which means I don't need to do this by locales.
  • 04:43 It's only coming in from text and CSV files, so
  • 04:46 I don't have to worry about using locale.
  • 04:48 So, in here, I can convert this to a date.
  • 04:50 That causes an error because it can't convert total 2015 to a date.
  • 04:55 So, now we can say right click Remove Errors, and
  • 04:58 we get those out of here as well.
  • 05:01 We can then go and set the data type as a fixed decimal number for
  • 05:04 our amount and our budget is now ready to load.
  • 05:09 We can say Home, Close and Apply.
  • 05:12 So this shows you some additional tools for how to go and clean up data.
  • 05:15 And you'll notice that once we've actually done this, if we go back to our data view,
  • 05:20 here is the budget table, here is the sales table.
  • 05:24 So we can flip back and forth and see which fields are out there for
  • 05:27 each of these particular locations.

Lesson notes are only available for subscribers.

Gift this course