ImportantThis article shows how the connector works with the Data Query 2 API.
About Power BI Desktop
Power BI Desktop lets you create intuitive reports for visual analysis of your data. Drag and drop content exactly where you want on your Power BI Desktop canvas. Quickly identify trends, thanks to a unified view and interactive visualisations.
- Transform and clean your data
Preparing data for analysis can take some time. Our objective is to simplify this task for you. Try Power BI Desktop’s data formatting and modelling features, and free up your time to concentrate on important data analysis.
- A complete analysis tool
Power BI Desktop is an end-to-end solution enabling you to analyse data with great precision. Power BI Desktop offers all features required to quickly connect, format, visualise and share data.
- Create and share your reports
We know that you need to share data with decision-makers whenever and wherever they need it. Power BI lets you easily publish and share interactive reports.
Learn more: https://powerbi.microsoft.com/en-us/desktop/
Start Power BI Desktop
To get started, simply download the desktop application at https://powerbi.microsoft.com/en-us/desktop/ and install it on your computer.
Once the application has been launched, you’ll be able to start importing data.
Import and format data
The application allows you to import from different data sources: Flat files, web data, or SQL Server.
Import Piano Analytics data
To import your Piano Analytics data into POWER BI, the simplest way is to use the web data connection feature, available in the drop-down menu (Get data > Web).
This will let you load a REST URL (in XML or JSON format) that you’ll obtain directly from within the Data Query application of Piano Analytics.
Build your query in Data Query, and select the REST API URL relating to your data set. Let’s go into Data Query.
We’ll select the URL that was created, below our data set.
Be careful to select XML or JSON format in your Data Query interface. Power BI does not handle the HTML mode
We’ll paste our Data Query URL (see below example) into the Power BI pop-up.
Authentication is required. Select the "Basic" mode and enter your Piano Analytics login and password.
Select data to visualise
Choose the rows and columns you wish to import (please note, importing columns is not always necessary).
To illustrate this User Guide, we’ll load 2 templates from Data Query as well as 3 data sets in Excel format.
"Data" view : format your data
Power BI Desktop also offers a Query Editor, which opens in its own window.
The Query Editor lets you create queries and transform data, then upload this template of filtered data into Power BI Desktop in order to create reports
Click on Edit queries in the Root folder tab.
Adjusting your data mainly involves transforming data, like renaming columns or tables, for example, or replacing text with numbers, deleting rows, defining the first row as a header, or programming which data will be summable or not, etc.
All edits made will be saved by the Query Editor. Each time this query connects to the data source, these same operations will be carried out, so that the data is always formatted in the same way.
This process will be done each time you use the query in Power BI Desktop, or for any person who uses your shared query, as in the Power BI service. These steps are captured, in a sequential manner, in the Query Settings window, under Applied Steps.
The above illustration shows the Query settings window which has been formatted. Note that in Query Settings, the Applied Steps section displays the edits made. To delete any step in the formatting process, simply select the step you want to remove, and click the X to the left of the step.
To make edits in the Query Editor and load them in Power BI Desktop, click Close and Apply in the home banner.
Other edits can be made after the table has been loaded, and you can re-load a template in order to apply the edits made. But for now, we’ll stop here. In the Report view in Power BI Desktop, you can start generating reports.
"Relationship" view: Connect several data sources to each other
Your business might benefit from the table merging features available in Power BI: manage relationships.
Let’s take an example with the CRM table below, which contains "city" information which is also available in the Cities table. Merging these 2 will enable us to make tables and graphs using all information contained in these 2 tables.
To do this, let’s go into the "Relationship" view.
Once you’ve merged two tables with a relationship, you can use the data from both tables as if it were a single table. In this way, you won’t have to worry about details of the relationship, and you won’t need to flatten these tables into a single table before importing them.
In many cases, Power BI Desktop can automatically create relationships for you. Nonetheless, if Power BI Desktop cannot determine with a high degree of certitude that a relationship between two tables must exist, it will not automatically create a relationship. In this case, you’ll have to create it yourself.
By clicking on the "Relationship" view, different potential relationships are automatically displayed, showing relations between one or several tables with a single-direction or bi-directional arrow.
To customise other relationships or edit existing relationships, click on "Manage Relationships".
A window will open. Click on "New".
A new "Create Relationship" window will open, letting you select tables between which to create a relationship.
In the drop-down list, choose among the created tables, and click on the columns to merge. Selected columns appear in grey.
It is now very important to choose the best cardinality.
Many-to-One (*:1): This is the default type, and the most common. This means the column in one table can have more than one instance of a value, and the other related table, often known as the Lookup table, has only one instance of a value.
One-to-one (1:1): This means the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value. For more information about modifying cardinality, please see Power BI’s online documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/ ).
When the "Make this relationship active" option has been selected, the relationship serves as the active, default relationship. If there are more than one relationships between two tables, the active relationship provides a way for Power BI Desktop to automatically create visualizations that include both tables.
For more information about making a specific relationship active, please see Power BI’s online documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/ ).
The new relationship will show up in your list of already existing relationships.
See a visual representation of relationships that have been created.
Use relationships in the "Relationship" view to visualise your creations. If you select fields from several related tables, their data will automatically be aggregated.
Here, for example, after merging the Cities and CRM tables, and then CRM and Profile, we get a dashboard listing the cities and regions of identified visitors, as well as profile qualification (beginner, average, expert).
"Report" view: format data
Build your report
You can reduce the "Visualisations" and "Fields" windows by selecting the small pointer arrow at the top right of each window. When you edit visualisations, you’ll also notice the presence of small arrows pointing up or sideways, which means you can expand or reduce these sections.
To create a visualisation, simply drag a field from the Fields list into the Relationship view. By dragging and dropping fields, you can format your data in a way that’s adapted to and recommended by Power BI.
Please note that the "Visualisations" window offers different types of graphs, so it’s possible to modify your graphs based on your needs.
Now that you have a relatively complete Power BI Desktop report, you can share it with other users of the Power BI service.
There are several ways to share your work:
- To publish on Power BI Online, load the file .pbix directly from Power BI Desktop
- Save the file .pbix and sen dit like any other file
Let’s start by publishing on the Power BI service directly from Power BI Desktop. In the home ribbon, select Publish.
You might be asked to log in to Power BI.
Once you’re logged in and the publishing process is complete, a dialog box will pop up will show successful action.
When you’re logged in to Power BI, the Power BI Desktop file that you’ve just loaded will appear in the Dashboards, Reports, and Data sets sections.
If any errors occur, please start again or try to publish your report using the second method.
Another way of sharing your work involves loading it from the Power BI service. The following link lets you access the Power BI service in a browser:
Click on Get data to start loading your Power BI Desktop report.
The Get data page opens, in which you’ll be able to select the source of your data. In the present case, select Get in the Files box. The Files view will appear. In our case, we’re going to select Local File.
Choose the file on your computer.
Once the file has been loaded, you can select it in Relationships, in the left-hand window of Power BI service.
Make the most of all your reports created in Power BI Online. To learn more about Power BI Online, please see the available user guide (contact the Centre Support).
Activating this partnership activates the export of Piano Analytics data into PowerBi. Only the data necessary for the proper functioning of the bridge are exported.