HTML formatting for Project Online Power BI Reports

[Ludia]

Microsoft made a change back in May 2016 to remove the HTML from the OData API, but since a new visualization (HTML Viewer) in Power BI released, the user hopes the text columns can be displayed with HTML format in Power BI.

But when i tried to connect the IncludeCustomFileds to get the original data with HTML script, i got an error message as below:

DataSource.Error: We couldn't parse OData response result. Error: A value without a type name was found and no expected type is available. When the model is specified, each value in the payload must have a type which can be either specified in the payload, explicitly by the caller or implicitly inferred from the parent value.
Details:
DataSourceKind=OData
DataSourcePath=https://*******.sharepoint.com/sites/pwa/_api/ProjectServer/Projects('d01ddd20-0a05-e711-80dd-00155de4d307')/IncludeCustomFields

Does anyone has any idea about this? thanks a lot

[Ludia]

Solved, will update the whole process later

[Ludia]

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-p...

Part 2
https://pwmather.wordpress.com/2018/01/03/projectonline-powerbi-report-include-html-formatting-ppm-p...

Part 3
https://pwmather.wordpress.com/2018/01/16/projectonline-powerbi-report-include-html-formatting-ppm-p...

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

[Ludia]

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"

[v-jiascu-msft]

Hi @Ludia,

Thank you for sharing. That's amazing.

Best Regards,

Dale

[Ludia]

Hi, @v-jiascu-msft,

Thanks a lot for your reply

Have a nice day 

Regards

Ludia

[AlexanderK79]

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/expressionerr... 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/expressionerr... 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... 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/34...

[goliash]

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