Building reports in Power BI Service

Meltwater offers a Power BI connector as described in this guide, however as this connector is not yet certified by Microsoft you cannot use the connector in Power BI Service. In this guide we will show you how you can use out-of-the-box Power BI features to build reports in Power BI Service.

Before you start

To complete this guide you will need:

Overview

For most customers the need to use Power BI Service stems from wanting to have automatically refreshing reports that can be shared with stakeholders. In this guide we will assume that you would like to bring data in and refresh that data on a schedule.

Below we will cover each type of data you might want to bring into your Power BI Service reports. The approach is the same in each case:

  • Use the built-in Web data source to call the Meltwater API and bring in the data
  • Modify the code generated by Power BI so that the latest data is fetched for each scheduled refresh
  • Build a report in Power BI Desktop
  • Publish the report to Power BI Service
  • Set up a schedule for refeshing the data

Using the Web data source to fetch data

Earned media exports

You can use the built-in Web data source to bring in the results of a recurring earned media export.

Firstly, make sure you have created your recurring export in the Export Console tool. Make sure when you create the export the refresh schedule matches your intended refresh schedule for the eventual report. For example, if you want a weekly refreshing report make sure you set the export schedule to run each week.

Next, right-click the JSON link for your recurring export and copy the URL.


In Power BI choose to create a new data source, selecting the Web option.


Paste the URL you copied for your export result into the settings for the Web data source and click OK.


Power BI will load the data from the URL and automatically detect the schema and data types for the data.

Generally Power BI does a good job of detecting the types for each data field, but it’s worth checking at this point that the types suit your analysis.

Unfortunately you will not be able to expand fields like keywords and key phrases alongside the document fields in one data source. If you do need this data, you can create another data source, expand the fields as you need, then join this second data source onto your first data source using the document ID as the join field.

Now that you have imported the data you can create a visualization and start building a report.

For earned media exports you do not need to modify the code generated by Power BI to bring in fresh data for each refresh. This is because the API overwrites results for each export run to the same URL. You can go straight to the Publishing to Power BI Service to continue.

Earned media analytics

You can use the built-in Web data source to bring in the results of an earned media analytics query.

Start by using the Analytics Console tool to find the URL for your required analysis. For example, in this case we have chosen to analyze the top key phrases for a search:


Open the “View API request code” area to see the request details and copy the URL for the request.

In Power BI choose to create a new data source, selecting the Web option.


Choose Advanced in the Web data source dialog, then paste in the URL you copied above. Also add a HTTP header called apikey and for the value insert your Meltwater API key.


When you click OK Power BI will call the API and fetch data for the analysis. It will take you to the Power Query Editor to see the results.

Generally Power BI does a good job of detecting the types for each data field, but it’s worth checking at this point that the types suit your analysis.

Now that you have imported the data you can create a visualization and start building a report.

As it stands, if you refresh the data the connector will fetch the analysis for the same time window as you selected in the Analytics Console. The steps in Modifying queries to fetch the lastest data below explain how to modify the code generated by Power BI to fetch the latest data for each refesh.

Owned social analytics

You can use the built-in Web data source to bring in the results of an owned social analytics query.

Unlike earned media analytics we do not currently provide a tool that helps build the URL for these queries. Please refer to the developer guide for details on how to construct a URL for a request.

As an example, the following URL would access a number of account-level metrics for a Facebook account:

https://api.meltwater.com/v3/owned/accounts/metrics/numeric?source=facebook&account_ids=[ACCOUNT ID]&start=2021-12-06&end=2021-12-12&metrics=page_fans,page_impressions,page_video_views

In Power BI choose to create a new data source, selecting the Web option.


Choose Advanced in the Web data source dialog, then paste in the URL for your request. Also add a HTTP header called apikey and for the value insert your Meltwater API key.


When you click OK Power BI will call the API and fetch data for the analysis. It will take you to the Power Query Editor to see the results.

Generally Power BI does a good job of detecting the types for each data field, but it’s worth checking at this point that the types suit your analysis.

Now that you have imported the data you can create a visualization and start building a report.

As it stands, if you refresh the data the connector will fetch the analysis for the same time window as this is hard-coded in the URL. The steps in Modifying queries to fetch the lastest data below explain how to modify the code generated by Power BI to fetch the latest data for each refesh.

Modifying queries to fetch the lastest data

For earned media analytics and owned social analytics, to have the data source fetch the latest data on each refresh you will need to modify the code generated by Power BI.

Open your query for editing in the Power Query Editor. Click on the Advanced Editor button in the Home ribbon menu to see the code Power BI has automatically generated.

The code will look similar to the following:


The following steps will change the code to dynamically create the web request.

Create a reusable time window function

The first step is to create a reusable function that will help select a time window based on the your current date and time.

Close the Advanced Editor window, to return to the main window. Then, from the Home ribbon menu select New Source then Blank Query.


Once the query has been created click on Advanced Editor in the Home menu ribbon. Replace the existing code with the following:

(period_days as number, optional period_time as time) =>
let
    _period_time = if period_time = null then #time(0,0,0) else period_time,
    end = Date.StartOfDay(DateTimeZone.LocalNow()) + #duration(0, Time.Hour(_period_time), Time.Minute(_period_time), Time.Second(_period_time)),
    start = Date.AddDays(end, -period_days)
in
    [ start = DateTimeZone.ToText(start, "yyyy-MM-ddTHH:mm:ss"), end = DateTimeZone.ToText(end, "yyyy-MM-ddTHH:mm:ss")]

This function returns a start and end date-time, based upon the parameters you provide and your current date and time. For example, if you provide 7 for the period_days parameter, it will return a start and end date-time which cover the last 7 days, ending at 00:00 of your current day. Note it also supports an optional period_time parameter which allows you to set a specific time rather than 00:00.

Click Done, then right-click on the query in the left-hand panel and choose to Rename the query as RelativeTimeWindowParameters.


Modify the Power BI code to use the time window function

Staying within the Power Query Editor window select your query that calls the API in the left-hand panel.

Click on the Advanced Editor button in the Home ribbon menu to see the code Power BI has automatically generated for the query.

Modify the code as follows:

  • Under let create a new line calling your new function, saving the result as a variable called time_window_params. The parameter for the function is the size of your time window in days.

    time_window_params = RelativeTimeWindowParameters(7),

  • On the next line you need to create a set of parameters you will pass to the API. This is the query string parameters from the URL you originally entered without the start and end parameters, combined with the result from the function above. In our example the original URL had the following query string; start=2022-05-17T00:00:00&end=2022-05-18T00:00:00&tz=Europe/London&company_id=5e303eb8d4b0100010239ffc&source=news&size=100. Notice how we express source, size, tz and company_id parameters in this line of code.

    params = Record.Combine({ time_window_params, [company_id="5e303eb8d4b0100010239ffc", source="news", size="200", tz="Europe/London"] }),

  • Finally, modify the line beginning Source = .... Change the first argument for the Web.Contents function to just https://api.meltwater.com, moving the rest of the URL minus the query string to the RelativePath parameter. Then add a Query parameter to the Web.Contents call, giving the params variable from above as the value.

    Source = Json.Document(Web.Contents("https://api.meltwater.com", [RelativePath="/v3/analytics/17967704/top_keyphrases", Headers=[#"apikey" = "a0191e7be2e35940f1aa826ac73b1e76"], Query=params])),

Your final code will look similar to the following:


Click Done to close the dialog, and test your changes by refreshing your data.

Technical note: The first param of Web.Contents function must be static, otherwise PowerBI thinks it’s a ‘dynamic data source’ and refuses to refresh it in Power BI Service. This is why we suggest modifying the code as described to avoid this issue.

Publishing to Power BI Service

Once you have built your report you can publish this to Power BI Service.

In the Home ribbon menu, click Publish to start the process.


Power BI will ask you to choose a workspace to publish to. Wait for the publish process to complete, then check that your report has been published successfully.

Set up refresh schedule

Now that you have published your report the next step is to set up a schedule that will refresh the data.

When Power BI publishes your report it will automatically create a Dataset in Power BI service. You can view your data sets by clicking on DataSets in the left hand menu of the Power BI Service application.

Find the data source for your report, click on the data source to access the data source settings, then click on Schedule refresh in the Refresh menu.


Scroll down to the Scheduled refresh settings and expand this area if it is not already shown.


Earned media exports

In the settings area you can set a refresh schedule that matches your schedule for your recurring earned media export.

As it can take up to 30 minutes for an earned media export to run, we recommend you set a refresh schedule in Power BI Service that allows for the export to finish. For example, in this case we set the schedule to refresh 1 hour after the export itself is scheduled.

Apply the settings and you are all set. Power BI Service will now refresh your data automatically and your report will show the latest data after each refresh.

Earned media and owned social analytics

In the settings area you can set a refresh schedule that matches your reporting need. For instance, if you would like the data to refresh each day you can create a schedule for this, such as scheduling a refresh every day at midnight.

Note that when you set up a schedule for analytics, Power BI Service may say your data source credentials are invalid or incomplete, even though you have included a valid API key in your code.

This happens because Power BI Service tried to validate your credentials by calling https://api.meltwater.com without a path to an analysis. Which is not permitted by the API as it is not a valid API route.

You can solve this by telling Power BI Service to skip its credential test. In the data source settings area, find the Data source credentials section, and expand this. Click on the Edit credentials link to edit credential settings for the data source.


Tick Skip test connection, and click Sign in to save this change. Power BI Service should now be able to refresh your data.