Monday, October 23, 2017

New PowerBI Project Online report pack: How to customize it on a non-english tenant?

Project team has just released a new report content pack. I won't go into the details: how to deploy it and plug it on your Project Online tenant. It has already been greatly explained, particularly by Paul Mather, a fellow MVP, in a recent post. Another relevant information: you can get the report pack on GitHub.

My contribution will be on another concern, which is related to my context: I'm located in France and working with international customers. Meaning that I often have French Project Online tenants, but I have to generate reports in English. And this is where the report pack has a weakness: it only works as-is on English tenant. So below is the procedure to deploy it on non-English tenant and customize it. 
Thanks by the way for my fellow colleagues to put me on track.

Once you have changed the data source (your non-English PWA tenant URL) as explained by Paul, it still doesn't work because the queries are looking for the english odata feeds. For example, all queries start with the source of the data:
    Source = OData.Feed(#"PWA Site URL" & "/_api/ProjectData"),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],

Thing is: on a French tenant, there is no "Project" feed, but "Projet". You can replicate this discrepancy on any queries, so you just get a bunch of errors. The solution is to add [en-US] to tell PowerBI to take the data source in english:
    Source = OData.Feed(#"PWA Site URL" & "/_api/ProjectData/[en-US]"),
    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],

You can do that by clicking on "edit query", select the queries one by one and click on "Advanced editor".

The second trick I want to share is how to personalize the reports. Obviously, the report pack is generic since it should be applicable for any Project Online instance. But you'll surely create custom fields (tasks, projects, resources). For example, for one of my customer, I added the "Region" custom field for projects. This is a key criteria I want to add in my reports.

For that, stay in the query editor and on the right panel, you should see for each query all the applied steps. Check for the "Removed other columns" step. This step is quite important in terms of performance: it will exclude from the query fields you don't need in the report to optimize the query time of processing. By default, all custom fields will be excluded.

When you click on the gear next to the step, you'll open a dialog box with all the available fields off the query. Then just click of the chosen column (custom fields) you want to include in your dataset.

When you close the query editor and apply the changes, you'll see the new custom fields in the available fields.


Et voilà!

2017-20-23: Note that as far as I noticed, there is a bug in PowerBI App where you cannot schedule a refresh on a dataset with [en-US] in the URL data source. I have a support case opened with Microsoft on that and will kept you informed.

Share this article :

2 comments:

  1. Hi Guillaume,

    You mentioned "there is a bug in PowerBI App where you cannot schedule a refresh on a dataset with [en-US] in the URL data source. I have a support case opened with Microsoft on that and will kept you informed."

    Did you ever resolve this with Microsoft because we have the exact same problem. You can e-mail my colleague at Neil.Ratcliffe@zurichinc.com as I will be on vacation now for a while, but he asked me to look into this.

    Many Thanks

    Jules

    ReplyDelete
    Replies
    1. I had the opportunity to dig a bit further and the bug has actually been fixed a while ago. So it should work correctly and I actually deployed PowerBI dashboards in french and english with no issue. Note that the Project ONline content pack now proposes to choose the language.

      Delete