In today's fast-paced business landscape, staying ahead of the competition requires efficient and effective solutions. According to Microsoft’s Work Trend Index, nearly 70% of employee report that they don’t have sufficient time in the day to focus on “work”, with more time being spent Communicating than Creating.
Microsoft 365 Copilot is designed, with Microsoft’s cloud trust platform at its core, to allow for employees to both be more productive, reduce the time spent searching for information, performing mundane tasks, and other low-value activities.
Power BI
Welcome back to my Power BI Beginner series! In my last blog post, I offered suggestions on the best tools and areas to help get you familiar with Power BI and the features that it offers. In the future posts in this blog series, I will begin to dive into particular areas of Power BI and explain how to implement features effectively within the Power BI service.
In this post, I will be discussing one of the most important parts of using Power BI: connecting to data sources!
The first step into getting started with Power BI is getting the data you need to begin modeling it and creating reports. Power BI is innovative in that it can connect to many different data sources ranging from Excel, CSV, JSON, or other text files, and it can connect to databases ranging from SQL Server databases, Azure SQL databases, Oracle databases, SAP databases, and many others. While it would take forever to cover how to connect to all of these different data sources, I will discuss how to connect to a few of the most commonly used data sources within Power BI.
Getting Data from Files
One of the most common places for businesses and organizations to export and store data is in file sources. The most common of these is a flat-file, which is a type of file that has only one data table and every row of data is in the same structure. In Power BI, people commonly use CSV or TXT files. Another common type of file would be the output files from different applications, such as Microsoft Excel workbooks.
The first step is to determine which file location you want to use to export and store your data. There are several options for storing your data from these files. You can use the file locally, which when imported into Power BI will create a new dataset. However, when you edit that file, it will not be reflected in Power BI since it is stored locally.
Another way you can store these files is by using OneDrive. OneDrive allows your files to be more synchronized and updates whenever you make changes to the files on your OneDrive. Another great method for connecting to these files is through SharePoint Team Sites. This is similar to connecting through your OneDrive, but SharePoint Team Sites are a popular use when working with Power BI on an organizational level.
Once you have selected where your data is stored and understand what source you will be connecting to, you will go into Power BI Desktop and begin the connection process. When you open up Power BI Desktop, you will begin connecting by clicking Get Data, as shown in the picture below.
Once you select Get Data, click File you will see all the different file sources that you will be able to connect to. As an example, I have selected an Excel file to show you what it looks like when you select your data source.
After you select your file and click Open, then Power BI will load up your file and you will be able to begin the transformation process on your data. Power BI makes it quick and easy to connect to an Excel file, CSV, JSON, or other flat files. This is extremely beneficial for analysts to use because it allows them to load up data from these files quickly and efficiently get reports.
Getting Data from Relational Databases
Another common data source that businesses and organizations use in their Power BI enterprise are relational databases. The most common of which is a SQL Server database or an Azure SQL database. SQL databases have been and continue to be the most commonly used amongst businesses because of their relatively easy use and their ability to handle large amounts of data. Power BI has the ability to connect to many different relational databases.
You can connect to these types of databases by clicking Get Data and then choosing SQL Server. SQL Server is one of the different types of relational databases you can connect to, but I will use it for this example.
Once you click on SQL Server, you will see the option to type in the server name as well as the database you are wishing to pull from. The great feature with relational databases is the ability to pull centralized data from the tables you are wanting to look at. Under Advanced Options, you can see the option to write a SQL query. This gives you the option to either pull your full table or to use a SQL query to get the data you’re focused on.
Getting Data from NoSQL Databases
While these data sources aren’t as common, businesses and organizations still have use cases in their Power BI enterprise. NoSQL databases can be imported into Power BI in an organized manner that allows you to analyze it more efficiently than many other reporting tools. Azure Cosmos databases can also be imported into Power BI, as well as JSON files. The key is that you have to extract the data from these databases and normalize them before you can begin transformations and modeling the data.
An example of extracting and normalizing can be seen in Microsoft’s documentation. Here is a JSON file that has been imported. To expand these records, you have to click Edit in the bottom right.
In Power Query, you can select the Expander button next to the column you are wanting to look at. Once you clear the Use original column name as prefix checkbox and click Ok, you can see all of the columns and data from the JSON file.
Conclusion
This is just one example of the ways you can import and interact with data in this form. My colleague Evan Gordon wrote an article a few months ago that shows a more in-depth analysis of importing this type of data. It can be found at the following link: https://blog.kizan.com/handling-mixed-type-columns-in-power-bi.
KiZAN is a Microsoft National Solutions Provider with numerous gold and silver Microsoft competencies, including gold data analytics. Our primary offices are located in Louisville, KY, and Cincinnati, OH, with additional sales offices located in Tennessee, Indiana, Michigan, Pennsylvania, Florida, North Carolina, South Carolina, and Georgia.
