HTML formatting for Project Online Power BI Reports

I got the problem solved with a very useful reference from Paul, here are the links from his post:

Part 1
https://pwmather.wordpress.com/2018/01/01/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-1/

Part 2
https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-2/

Part 3
https://pwmather.wordpress.com/2018/01/16/projectonline-powerbi-report-include-html-formatting-ppm-pmot-powerquery-odata-rest-part-3/

Viewing the detail information using IE with the link doesn't work for me, so the whole process is conducted in Power Query:

Here are my steps:

1. Create a new Odata Feed query with (taking 1 specific project id to conduct:

 https://********.sharepoint.com/sites/pwa/_api/ProjectServer/Projects('d01ddd20-0a05-e711-80dd-00155de4d307')

2. After you got the detail information for this project, Intotable for "CustomFields":

3: You will get the Intername Corrsponding the name "Status Update"

4:  Get a REST URL for one project that includes custom fields, for example I have used this: https://*******.sharepoint.com/sites/pwa/_api/ProjectServer/Projects(‘d01ddd20-0a05-e711-80dd-00155de4d307‘)/IncludeCustomFields?$Select=Id,Name,Custom_888a2aeba276e61180cf00155de4ce03

(Replace the red part using your own data)

you will get the result as below: ( this picture is from my project, but exactly same thing)

5: Update the Query Name to something like projectHTMLCFsFunction as this query will be turned into a function. In the Query Editor, on the View tab access the Advanced Editor and you will see your query:

6: Make it into a funciton with the below script:

let loadHTMLCFs = (GUID as text) => 
    let 
        Source = OData.Feed("https://*****.sharepoint.com/sites/pwa/_api/ProjectServer/Projects('"&GUID&"')/IncludeCustomFields?$Select=Id,Name,Custom_888a2aeba276e61180cf00155de4ce03") 
    in 
        Source 
in  loadHTMLCFs

7: click New Source > OData feed and add in the OData Reporting API URL: https://******.sharepoint.com/sites/pwa/_api/ProjectData, then select the tables required

Add a custom column with the below definition: 

projectHTMLCFsFunction is the name of the function we created earlier and we are passing in the ProjectId. When clicking OK, this might take a while depending on how many projects you have as this will invoke the function for each project and call the REST API, passing in the ProjectId for that row and bring back the records. Once completed you will see the records as below in the new custom column:

Now the column needs to be expanded, click the double arrow in the custom column heading and expand the multiline custom fields, in this example I just have one:

Click OK and the data will refresh / load then display the data for the multiline columns:

Then you can get the HTML format using HTML Viewer visulization:

Done :-) 

Many thanks to Paul Mather, a little bit difference in the process, but very helpful for me