Module 3: Importing Data from a CSV File

Module 3 > Importing Data from a CSV File > Lecture

• This is module three from the course about analyzing and visualizing data in Excel.
• In this module I will introduce you to queries in Excel that are done using a technology that was previous to 2016, which I’m using, known as Power Query.
• You’ll see it as functionality of the Power Query add-in in Excel.
• It’s there side by side with older functionality to import data in Excel.
• Further down in the next versions of Excel we plan to make this technology that came from Power Query, it is now not called Power Query any more, the default technology to import data.
• For now it’s there side by side and I’m going to use it exclusively to bring data into Excel.
• My first example in bringing data from using the query will be very simple.
• This table has everything I need, not necessarily ready in the shape and the form that I need it but I will form it and shape it using the query.
• In the data ribbon, there at top in the ribbon, you see there’s an area here what we call a chunk and it’s title is Get and Transform.
• Power Query is a technology that is designed to bring the data into Excel.
• It can bring data into the Excel grid as tables, and you could then use them for any use that you want in Excel.
• Or it can bring data directly into the Excel data model that was introduced in the previous part of this course.
• It can actually bring data to both, which I would not recommend you to do, but if you want to it’s possible.
• So I would say I want to query, to create a new query from CSV file.
• Now what I will see next is the query editor, so this is an environment in which I am going to enhance the query, create the query and then I can save the query, and every time I need I can run it again to refresh the data to bring the data from the same source again.
• There’s still a few things which I need to do in order to prepare this data.
• Actually, I don’t need all this superfluous data.
• So I’m doing a series of simple steps to make this data ready for its purpose to be used later as the basis for my pivot tables, so far nothing really fancy.
• Now another thing is that the state, I actually only have data from two states here, New York and Texas, but the state comes as abbreviations.
• The good thing is if you look at the right side here, you see here the query settings here, one is that name of the query.
• This is actually each one of them is one of the steps that I did while I working on this data.
• So I removed columns and each time I click on one of them, I actually see the way the data looks after the step.
• Next time, I will run the query by clicking refresh on the table that I will create.
• So here we saw a list of very simple steps, later on we’re going to see there’s a few more parts about using queries.
• It looks ready to me, so it can go and from here I have Close and Load or Close and Load To. Close and Load To enables me to choose the target for this data when it’s going to be and there’s two parts to it.
• The second part is about this checkbox, do I want it in the data model? The fact the default comes this way is because I configured before.
• You see here the two parts and now I don’t need to change anything, I can click Load. And on the right side you see the progress of this query and it says that they have 33,000 and something loaded.
• When I hover over it I see a preview of the data and we don’t see any of the data because it’s actually in the data model, this is where I asked it to go to the data model.
• I can go in here, insert a pivot table, again the default for 2016 is use this workbook from the data model.
• I can actually go and create a pivot based on this data.
• Previously known as Power Query in Excel 2016 to bring flat CSV file.
• In the next modules we’ll see building of more complex data models also using queries.
• Then eventually we’ll also go dive into DAX and create more sophisticated logic within these data models.