As we know, data has a lot of different forms before we can retrieve and bring it in Power BI. Power Query offers several advantages when it comes to importing and consolidating Power BI data. In this blog, we’ll discuss the different ways on how we can bring data in Power BI.
Think of Power Query as an ETL (Extract, Transform, Load) tool.
Bringing Data In Power BI Via Query Editor
The first way is by clicking the New Source option. Then select More…
This will show us more data options that we can retrieve and are available on our desktop like a text file, folder, SharePoint folder, Access databases, and many more.
For instance, there may be some custom system where Power BI doesn’t have a custom connector. Those systems could be exported to an Excel or a text file. Then we can retrieve it from there. This is where we can do the ETL process to get the data into some type of form that we can use in Power BI.
Power BI also covers a lot of different databases like the SAP systems and all these Azure examples.
We also have some custom connectors like Google Analytics, Salesforce, different social medias, and many more.
Some of the options are so unique that we can’t probably demonstrate them because of the uniqueness of their table structure. I personally don’t understand them all but in terms of connecting, it’s all relatively similar.
For example, let’s select Google Analytics and connect it.
We’ll sometimes encounter some warnings when connecting. For this example, we just need to click the Continue button.
Then, it’ll just show a window that asks us to sign-in. Once we log in, it’ll show us all the different table structures underneath it that we can connect to.
Connecting To Different Data Source In Power BI
Let’s go through other examples like connecting to a Folder.
Once we connect to a Folder, it’ll show us a window where we can enter a folder path. Then, it’ll show us all the underlying folders that we can connect to.
Additionally, we have a Text File option.
This allows us to easily locate a text file anywhere in our computer.
The main point here is that we’re not restricted to only using Excel files for bringing data in Power BI because there are a lot of other options that we can use.
Another example of importing data in Power BI is by connecting to a website.
We only need to type the URL which will allow us to connect a data table within it.
How To Bring In Data From The Advanced Editor
Another way of bringing in data to Power BI is via Advanced Editor.
Here’s a code that we can copy and paste into the Advanced Editor. This code will provide us with a date table.
We first need to create a blank query by clicking the Blank Query under the New Source option.
Then, a new Query will be created. After that, click the Advanced Editor option.
Remove the existing code here.
Then, paste the code inside the Advanced Editor and click the Done button.
This will allow us to create a parameter query. For this example, we’ll add a sample StartDate, EndDate, and FYStartMonth. Then, click the Invoke button.
As a result, it gave us a Date table. So, that’s how we can create a data table using the Advanced Editor.
Conclusion
In a nutshell, there are lots of options to retrieve data and bring it in Power BI. The key thing to note here is to always do it inside the query editor. By doing that, we can have a look at all the data and optimize them.
We need to optimize our tables before we model them in Power BI especially if our tables are large. Another important note is that we can also connect to multiple sources. Hence, we don’t need to connect to them one at a time.
Indeed, the query editor is such an important place to consolidate, transform, and optimize those tables to build a great data model in Power BI.
All the best,
Sam
***** Related Links *****
Using Power Query Advanced Editor To Extract Values Before A Specific Text
Power Query Editor Tutorial: Extract Record Field Values From Lists
Power Query Power BI | Create New Records Based On Date Fields
***** Related Course Modules*****
Ultimate Beginners Guide to Power BI
Advanced Data Transformations & Modeling
Power BI Super Users Workshop
***** Related Support Forum Posts *****
How To Generate Queries In Query Editor With M Code
Extract Data From List In Query Editor
Error Handling In Query Editor
For more query editor support queries to review see here….
