Exporting data to PowerBI

Conjointly makes it easy to export survey data to PowerBI for further analysis and visualisation. You have two options to export your data into PowerBI (similar to exporting data via API):

• The simplified mode gives you respondents’ information and answers to survey questions only for responses currently used in the report, excluding any monadic blocks. Importantly:
  • The respondent dataset updates when you recalculate the report after new responses are collected, and only includes respondents with “Included in the analysis” status under the Included in analysis? column in participant list.
  • If you have not yet run the report, the dataset will be empty, and you will need to run the report at least once to populate the dataset with respondents’ information and answers to survey questions.
• The full mode gives you all respondents’ information and answers to survey questions, including those from monadic blocks.

Preparation for exporting data to PowerBI

1. You need to create an API key from the Access tokens page.
2. You need to have an active Professional or Ultimate licence.
3. You need to have a PowerBI account.
4. Go to the experiment settings page of the experiment you want to export data from, and click on the Other actions button at the bottom left side of the page. From the dropdown menu, select Set up export to PowerBI to open the export settings pop-up.
5. In the pop-up, select:
   • Choose your update frequency (e.g. hourly, daily, or weekly).
   • Select the API token you generated in step 1.
   • Click Enable export to PowerBI to save your settings and generate the export URL.

Steps on the side of PowerBI

In PowerBI:

1. Click Create → Get data.
2. In the modal window, under “New sources”, choose Web API (which can be placed under “View more” or “Other” category).
3. Fill in the settings:
   • URL: the URL corresponding to the mode that you chose
   • Connection: Create new connection
   • Connection name: Type your desired name for the export
   • Data gateway: none
   • Authentication kind: Basic
   • Username: your e-mail address used for your account on Conjointly
   • Password: the API token you generated in step 1
4. Click Next
5. In the Power Query window, under the menu “View”, choose “Advanced editor” (or switch on the “Query script” mode):

6. Enter the following script into the window (this step is required to make proper mapping of JSON to tabular data):

let
  Source = Json.Document(
      Web.Contents("網址"),
      65001
    ),
    ColumnMapping = Source[columns],
    OutputData = Source[output],
    ConvertToTable = Table.FromRecords(OutputData),
    GetColumnName = (value) => 
      if value is list then List.Last(value) 
    else value,
    RenamedColumns = Table.RenameColumns(
     ConvertToTable,
     List.Transform(
       Table.ColumnNames(ConvertToTable),
         each {_, GetColumnName(Record.Field(ColumnMapping, _))}
       )
    ),
    
    DatetimeColumns = {
      "Datetime of participant creation",
      "Datetime of entering survey",
      "Datetime of finishing survey"
    },
    
    ExistingColumns = Table.ColumnNames(RenamedColumns),
    
    ColumnsToTransform = List.Select(
      DatetimeColumns,
      each List.Contains(ExistingColumns, _)
    ),
    
    TypeTransformations = List.Transform(
      ColumnsToTransform,
      each {_, type datetime}
    ),
    
    TypedColumns = if List.IsEmpty(TypeTransformations) 
      then RenamedColumns 
      else Table.TransformColumnTypes(RenamedColumns, TypeTransformations)
in
  TypedColumns

7. Click Create to apply the script and load the data. PowerBI will prompt you where to save the model and the report.

List of active PowerBI exports

You can view the list of active PowerBI exports for your experiments on the PowerBI exports page.