To begin, click on the "Get Data" button on the Home tab of Power BI. This will bring up a new window with several options.
Connecting to an Excel Workbook
To import data from Excel, select the Excel option and click "Connect". Power BI will then allow you to select a file and will connect to it as the data source. Once connected, it'll show you all the tabs that contain data on them, as well as any named ranges you may have (tables, and other defined cells).
Check the boxes for whichever ones you need. From here, you can either "Load" or "Edit". Choose "Load", and Power BI will load the data into the model.
What's the M Code?
For the M code, there are actually two steps in this process.The corresponding code to import an Excel file is this:
= Excel.Workbook(File.Contents("your file.xlsx"))
- First, File.Contents() returns a binary of the file.
- Second, Excel.Workbook() then converts it into a viewable table for us.
Note: If you try to import a ".xls" workbook with a 64 bit version of Power BI, you will get an error. You either need to use a 32 bit version of Power BI or change the file to a ".xlsx" file. Another option is to use Python, which I discuss in my blog, Getting Data with Python.
Connecting to the Web
To import from the web (Wikipedia for example: Video Games Considered the Best), you can simply choose the "Web" option instead of Excel. Enter the web URL when prompted and Power BI will connect to the web page.
If the page has a table or list structured in the html, Power BI will pick up on that and who you in the preview area as a "List".
What's the M Code?
Similarly to importing an Excel file, the web connector has 2 parts to it for the M code:
= Web.Page(Web.Contents())
- First, Web.Contents() pulls in the html in as a binary.
- Second, Web.Page() converts the binary to a readable table.
To read more about connecting to the web with M (Power Query Mashup Language), check out my blog post, Analyzing the Best Video Games.
Get Data From SharePoint
Since SharePoint is a website, so you can use the Web connector here as well. In the URL entry field, enter the full URL address of your SharePoint site including the file name with extension. For example:
"https://yourcompany.sharepoint.com/my report center/my folder/my file.xlsx"
Power BI may ask you to enter credentials before it connects. Once connected, Power BI will pick up on the Excel file extension and import it accordingly. From here, you can follow the on-screen prompts to get the data you need.
Power BI also has 3 types of data connectors for SharePoint. You can type in the search bar on the Get Data window to find these options:
Each option will require you to enter the root SharePoint URL. Power Query will give you an error if you include any extensions.
What's the M Code?
Even though there are 3 options, the "List" and "Online List" do the same thing from what I can tell. If we take a quick look at the M code that is being generated for these options, they both have the same function:
= SharePoint.Tables()
Whereas the "SharePoint Folder" options has this (rather confusingly named) M code:
= SharePoint.Files()
Get Data From PDF
Did you know Power BI can import data from a PDF!? As a previous Accounts Payable worker, I know a lot of companies receive invoices as PDF's, and people like to save documents as PDF's as well. As of this writing, PDF data source is still in Beta mode, so you may need to enable this feature in the "Preview Features" section in Settings.
Power BI will do it's best to pick out any structured tables that are on the PDF. If it can't, it'll give you the option to grab the entire document.
What's the M Code?
The code here is fairly simple and straightforward. It actually uses one of the functions above for getting Excel data:
= Pdf.Tables(File.Contents())
- First, File.Contents() gets the data as a binary.
- Pdf.Tables() converts it to the table for us.
Let me know
if you have a questions about getting your data in Power BI! I only covered the basics here, but other sources should be fairly similar.
Sometimes column names can change at the source, causing errors in Power Query. Check out my blog on preventing column name errors.
If you want to learn more about M code, please check out the Microsoft documentation. They list out all of the M functions with examples and is a great resource.
Recommended Reading:
Having a solid understanding of DAX is essential for Power BI users. I recommend reading "Supercharge Power BI" for anyone wanting to get a better grasp on the subject. Matt does an excellent job at breaking it down into easily digestible parts.
