Module 3: Importing Data from a CSV File
“Importing Data from a CSV File … Importing Data from a CSV File”
- Module 3 > Importing Data from a CSV File > Lecture
- Module 3 > Importing Data from a CSV File > (Optional) From PDF to Data Model using Flash Fill
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.
Module 3 > Importing Data from a CSV File > (Optional) From PDF to Data Model using Flash Fill
- So what we can do here is highlight the columns in the table we want to copy, Ctrl C, and going to excel, do Ctrl V. And here the problem is that all the data comes into one column and we want in three columns.
- I can highlight the entire table and convert it to a table.
- Insert table, my table has headers, yes, and I’ve created my table, I’m going to give it a name here, call it Sales.
- So in the interest of time, I’ve used the exact same process to create four more tables from that PDF that are just on the right of my newly created table.
- These tables tell me the number of car dealership in that state, so Alabama has 297 car dealerships the number of employees selling car Alabama has around 14,000 which gives them an average of 47 per dealership.
- The weekly earnings on average of those employees $870 and in order to be able to join all those states we created mapping table with state which I also group it into different regions.
- So I need to add my newly created table to my data model, so I click in a cell in the table, click on PowerPivot and click Add to data model.
- So I’m going to join all those tables together through my mapping table that has state and region.
- I go from state in NewCarDship to state mapping table.
- Four tables with data joined together through this mapping table.