HTML formatting for Project Online Power BI Reports

Solved, will update the whole process later

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

Another solution: replacement of the funcitons for each column, you can add all of them in one M query:

let

    Source = OData.Feed(ProjectOnlineUrl),

    Projects_table = Source{[Name="Projects",Signature="table"]}[Data],

    #"Добавлен пользовательский объект" = Table.AddColumn(Projects_table, "Cost at Completion", each [ProjectActual]+[ProjectForecast]),

    #"Измененный тип1" = Table.TransformColumnTypes(#"Добавлен пользовательский объект",{{"Cost at Completion", Int64.Type}}),

    #"Добавлен пользовательский объект1" = Table.AddColumn(#"Измененный тип1", "Variance Status", each [ProjectBudget]-[Cost at Completion]),

    #"Измененный тип2" = Table.TransformColumnTypes(#"Добавлен пользовательский объект1",{{"Variance Status", Int64.Type}, {"ProjectBudget", Int64.Type}, {"ProjectActual", Int64.Type}, {"ProjectForecast", Int64.Type}}),

    #"Измененный тип" = Table.TransformColumnTypes(#"Измененный тип2",{{"Cost at Completion", Int64.Type}}),

    #"Renamed Columns" = Table.RenameColumns(#"Измененный тип",{{"ProjectBudget", "Budget"}, {"ProjectActual", "Actual"}, {"ProjectForecast", "Forecast"}}),

    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([ProjectIdentifier] <> null)),

    #"Added Test_Column" = Table.AddColumn(#"Filtered Rows","Test",each OData.Feed("https://****.sharepoint.com/sites/pwa/_api/ProjectServer/Projects('"&[ProjectId]&"')/IncludeCustomFields?$Select=Id,Name,Custom_888a2aeba276e61180cf00155de4ce03")),

    #"Expanded Test" = Table.ExpandRecordColumn(#"Added Test_Column", "Test", {"Custom_888a2aeba276e61180cf00155de4ce03"}, {"Custom_888a2aeba276e61180cf00155de4ce03"})

in

    #"Expanded Test"

Hi Ludia,

Thank you for sharing. That's amazing.

Best Regards,

Dale

Hi, v-jiascu-msft,

Thanks a lot for your reply:smileyvery-happy:

Have a nice day 

Regards

Ludia

and this is an approach that refreshes correctly in PBI Service

Based on the previous posts and the work from: https://social.technet.microsoft.com/Forums/en-US/5d7ca0c6-36a1-4504-be74-5334fb498e8d/expressionerror-we-cannot-apply-field-access-to-the-type-list?forum=powerquery for the Web.Contents query instead of ODATA

This is a solution for retrieving data from Project Web App (Project Online) that works when refreshing in PowerBI Services:

It works with the Json.Document(Web.Contents( function instead of OData.Feed

// replace xxx with your tennant in the URL: https://xxx.sharepoint.com/sites/pwa/_api/ProjectServer/CustomFields
// replace Field_x0 and Field_x1 with the names of the fields in Project Online
// replace NewName_x0 and NewName_x1 with the names that you want to give to the new columns
// retrieve the custom fields (Custom_x0 and Custom_x1 in this code) that contain HTML in Project Online using:
// https://xxx.sharepoint.com/sites/pwa/_api/ProjectServer/CustomFields

let
PWA_url = "https://xxx.sharepoint.com/sites/pwa",
ProjectsRelativePath = if blReducedSet then "Projects()?$Filter=ProjectType ne 7 and substringof('Test', ProjectName) eq true" else "Projects()?$Filter=ProjectType ne 7",
Projects = Json.Document(Web.Contents(PWA_url&"/_api/ProjectData/[en-us]/",
[
Headers=[ #"Accept" ="application/json" ],
RelativePath = ProjectsRelativePath
])),
ProjectsList = Projects[value],
#"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", {"ProjectId", "ProjectName", "Field_x0", "Field_x1"}, {"ProjectId", "ProjectName", "Field_x0", "Field_x1"}),
#"Added HTML source" = Table.AddColumn(#"Expanded Column1", "HTMLsrc", each
if [Field_x0] is null and [Field_x1] is null then
Record.FromList({null}, {"d"})
else
Json.Document(Web.Contents(PWA_url&"/_api/ProjectServer/",
[
Headers=[ #"Accept" ="application/json;odata=verbose" ],
RelativePath = "Projects('"&[ProjectId]&"')/draft/IncludeCustomFields?$Select=Id,Name,Custom_x0,Custom_x1"
]))
),
#"Expanded HTMLsrc" = Table.ExpandRecordColumn(#"Added HTML source", "HTMLsrc", {"d"}, {"d"}),
#"Expanded d" = Table.ExpandRecordColumn(#"Expanded HTMLsrc", "d", {"Custom_x0", "Custom_x1"}, {"Custom_x0", "Custom_x1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded d",{{"Custom_x0", "NewName_x0"}, {"Custom_x1", "NewName_x1"}})
in
#"Renamed Columns"

// based on https://social.technet.microsoft.com/Forums/en-US/5d7ca0c6-36a1-4504-be74-5334fb498e8d/expressionerror-we-cannot-apply-field-access-to-the-type-list?forum=powerquery for the Web.Contents query instead of ODATA
// based on https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/ for solving the dataset refresh error
// also inspired by https://community.powerbi.com/t5/Issues/HTML-formatting-for-Project-Online-Power-BI-Reports/idi-p/341950

Hi, AlexanderK79

I´m using your Query:

Expression.Error: We cannot convert a value of type Function to type Logical.
Details:
Value=[Function]
Type=[Type]

Could you plase share the

blReducedSet 

?

Thanks

Goliash