Exporting data to Power BI

Conjointly makes it easy to export survey data to Microsoft Power BI for further analysis and visualisation. You have two options (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 Power BI

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 Power BI 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 Power BI 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 Power BI to save your settings and generate the export URL.

Steps on the side of Power BI

In Power BI:

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. Power BI will prompt you where to save the model and the report.

List of active Power BI exports

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

Using a different BI/data visualisation tool?

While this guide is written for Power BI, the underlying mechanism (an authenticated JSON API endpoint) is compatible with most modern BI or data visualisation tools that support REST API connections.

Please note that the JSON data from this endpoint is structured with separate arrays for column names and data rows. Your chosen tool will need to parse and map these together, similar to how the Power Query script automatically handles this mapping in Step 6 above.

Similar approaches are available for:

• Tableau: Connect via a Web Data Connector (WDC) or use Tableau Prep, to authenticate via Basic Auth and map the JSON arrays into a tabular format.
• Looker Studio (formerly Google Data Studio): Looker Studio can connect via third-party connectors designed for REST APIs. Alternatively, you can pull the data into Google Sheets first, then connect Looker Studio to that sheet.

Note that Conjointly does not provide platform-specific guides for these tools, so configuration on your end will be required.