Integrations‎ > ‎Microsoft Power BI‎ > ‎

Data preparation

If the Power BI is successfully connected with your Esensorics MySQL database it need some tweaking before you can start to build your reports.

Start by right clicking on qlity_sample_fields_flattened table name and select Edit query from the dropdown menu.

Find answserKeyValue and data column and make sure it is number not text.
It should be "1.2" not "ABC"
If its "ABC" then right click on the column header, select "Change type" from the dropdown menu and select "Decimal Number"

Repeat the same process with qlity_user_fields_flattened table.

You also need to create new column into qlity_user_fields_flattened and qlity_sample_fields_flattened table that concatenates qlitySurveyid and userid columns. This is needed to connect the tables into one data set. 

Create a new column called SID by using CONCATENATE formula to join qlitySurveyid and userid columns.

Example code looks like that but might be a bit different depending of your database table names.

SID = CONCATENATE('account_3 qlity_sample_fields_flattened'[qlitySurveyid];'account_3 qlity_sample_fields_flattened'[userid])

NB! Create the new SID column into both: qlity_sample_fields_flattened and qlity_user_fields_flattened tables.

You also need to create one additional table that would be based in list of distinct SID values. Create new table by using formula:

SID = DISTINCT('account_3 qlity_sample_fields_flattened'[SID])

When all this is done move to Relationships tab.

Delete the default relationships created by Power BI.

Connect database tables by using following schema:

And your done!