In several previous “recipes” I wrote about how to connect to Microsoft SQL Server from Excel in order to retrieve data to a worksheet. These were all “innocent” operations, since you could only read the data from the database, not to change them. Sometimes it is necessary to create a program that performs more complex manipulation over the database by running appropriate SQL query, which will be discussed in the text that follows.
Power Query is indeed powerful Excel add-in that allows retreiving data from different locations, changing them and preparing for further processing. Data sets are retrieved by creating queries to the data source, and the result of each query is stored in a separate table. Sometimes it is necessary to merge the results of two queries. The following text will explain how this works.
PowerBI Desktop application allows you to import data from many sources: file, database, BI solution, “Big“ (unstructured) data, Microsoft “cloud” (Azure), the web page … Given the fact that there is no good report without quality data I think it is appropriate to devote one recipe especially to this problem. Below, I’ll show you how, with the help PowerBI Desktop you can import, purge and combine various data.
In one of the earlier posts I wrote about that PowerBI comes in many “flavors”: as a Cloud Service (PowerBI.com) as a development environment (PowerBI Desktop), as application for mobile devices available on Windows, Android and iOS platforms (PowerBI Mobile). Since the majority of you who are reading these lines deal with reports development I decided to demonstrate you, in a practical example, how to use PowerBI Desktop.
We use Data model, in which we add and connect several tables, to create reporting dataset. In one of previous recipes we were talking about creating links between tables by dragging & dropping keys. The subject of this post will be manual modifying, changing properties or removing data connection. Also you’ll be able to see properties of tables imported from other data sources, such as MS SQL, MS Access etc.