Hi! Welcome to my channel,
Bsmart2gether. You can call me KT. I hope you enjoyed my previous video,
"Introduction to Power Query."
If you haven't watched it yet. Then, this post has a link attached to it.
And I strongly recommend you to watch it. It's a short minutes video.
Today, I will show you how to import data into Power Query.
Stay tuned, and let us begin.
It would be great; if you are able to open excel and follow me throughout this video.
I have attached the workbook I use in this video.
Once you have it open.
"Get and transform data" can be found in the top left corner of the Data tab.
That is where the Power Query was located.
Hover your mouse to the top left corner.
Click on "Get Data"
There are data import options,
Power Query settings and Power Query Editor are on the list.
I will take a few moments to go through most of them.
The first one from the list is "From File".
Under "From File", you have the option to import data from Workbook, Text or CSV files,
XML, JSON, Folder and Sharepoint Folder.
The PDF option is not on my list for technical reasons. However, you should have the PDF option.
Most of the options under From File are used often.
The next one from the list is "From Database". Before you can connect and import data, you would
usually need to request database access from the administrator. It is for security reasons.
The next one from the list is "From Azure". The Azure option is similar to the database option.
You have to request access from the administrator for security reasons.
The next one from the list is "From Online Services". The Online Services
option is less commonly used unless your workplace uses Dynamic 365 or Salesforce.
The last one from the list is "From Other Sources". I use some of the "From Other
Sources" options a lot like import data "From Table or Range" and "From Web".
I will cover the remainder list in future videos,
but I would like to cover " Power Query Editor" briefly.
So, what is Power Query Editor?
It is where you edit and transform your import data.
Each of the data imports is known as a query.
Let's take a closer look into it.
We have Ribbons and Query Editor Toolbar, Formula Bar, Query Pane, Query Settings, and Preview.
In the top corner, there are four tabs: Home, Transform, Add-Column, and View.
There are various functions available under those tabs to power you with your data transformation,
such as deleting columns and rows, adding conditional columns,
and so much more. We will go through the details with examples in future videos.
In the last few minutes, we talked about various import options.
Let me show you a few "Data Extraction" examples that you can apply at work daily.
The first one is how we import data from another Excel workbook.
Now, let's hover your mouse over to the top left corner and click on "Get Data",
"From File", and then "From Workbook". You will see a pop-up "File Browser".
Select the file you want to import and click "Import".
From the "Navigator" screen", select the sales worksheet, and then click on transform data.
Now your data is imported into Power Query Editor! To save it, hover your mouse over to the top left
corner. Next, click on the "Close and Load" and select "Close and load to".
Here are a few options. You can save your imported data as a table to either
an existing worksheet or a new one, as a Pivot Table Report, Pivot Chart,
or Connection Only. Also, you can add your imported data to the data model.
In this exercise, we will load the imported data as a table.
Your imported data will load into the worksheet as a table. Save your workbook to save your work.
Congratulation!! You just learned how to extract data from another workbook and load
it into the current workbook. Now you can claim yourself know how to use Power Query!
Hang on! Please don't leave!
I have more to show you!
We were talking about a few load options. Now we loaded it as a table. What if we need to change?
To change the load option, Then select "Load To". And now change it to the right option.
To load it, you need to click OK. Because you load it as a table before,
and change of option will delete an existing table. Click OK to continue to load it.
Now it is loaded as a Pivot Table Report option.
If you need to change the load option, this is how you do it.
Let's change it to "Connection Only" this time.
Right-click on the "Sales" query and click "Load To".
Change it to "Connection Only", then click OK to continue.
Click OK from the pop-up, and now we have the pivot table report removed.
I am not covering the data model in the Power Query tutorial,
but let me show what it looks like if we load our data into the data model.
Right-click on the "Sales" query and click "Load To".
Tick the "Add this data to the data model" box, then click OK to continue.
Go to the "Data" tab, and click on the "Manage Data Model".
Here you go! Here is the data we added into the "Data Model".
"Data Model" is also known as the "Power Pivot".
Click the cross in the top right corner to close it.
The next one I am showing you is importing data from a text or CSV file.
Now, let's hover your mouse over to the top left corner and click on "Get Data",
"From File", and then "From Text or CSV". Or, you can use the shortcut from the toolbar.
Select the sales data text file from the pop-up file browser, and click import.
I clicked "Transform Data" in the previous example,
and it took us to Power Query Editor. I want you to click "Load" this time to load the data.
Now the data is loaded as a table, which is the default load option.
The last data import option I will show in this video is "From SharePoint".
There are two ways to import it. The first one is "From Web". Click on "Get Data",
"From Other Sources", and then click "From Web." Or you can click it directly from toolbar.
Another way to import data from SharePoint is "From SharePoint Folder".
Click "Get Data," "From File", and then click "From SharePoint Folder".
If you are importing multiple files, you need to use "From SharePoint Folder";
however, it is much easier to use "From Web" to import a single file.
I will show you to import "From Web" in this video.
Click "From Web" directly from the toolbar, and you need a URL to proceed.
So, where do we get the SharePoint URL? Go to the SharePoint site where you saved
your file. Select the file, and click the three dots. Now choose "Detail" from the list.
An information pane will pop up on the right-hand side. Scroll down to the end
within the information pane, and click the copy icon to copy the direct link.
Now go back to Excel, paste the direct link into the URL field, and click OK.
From the pop-up, click "Sales", and then click "Load".
Here you go! Data loaded!
So where are we in this video? You are here!
We are covering Data Extraction and Load to Power Query.
So what are other videos coming next? We will talk about data transformation
in the next video, and will follow by the merge and append.
I will also cover some basic error handling, and Power Query Settings and Options.
We covered various methods to extract and load data into Excel.
What you have learned today allow you to apply them immediately.
Also, I will cover a few more with examples in future videos.
Thank you for watching. Please click subscribe and turn
on the notification to avoid missing useful videos. Click like if you like the video,
or feel free to drop a comment for anything excel challenge you have, and you like to know
what is the best way to deal with it. I will try to create some example videos in future.