Errors received in report query for custom issues list

[Anonymous]

Hi, Apologies as this is probably really simple, but I'm definitely not technical. I have a Project Web App site with over 100 projects. We've been using it for some time and are now expanding our use of the functionality. The majority of these 100 projects does not have a SharePoint site sitting behind it and this is something we're looking into using from now on, mainly to make use of the risk and issue functionality. I've been following this excellent guide (https://pwmather.wordpress.com/2016/01/05/want-to-query-cross-project-site-sharepoint-lists-in-proje...) on how to report on the custom fields I have created for my Issues list. I am however stuck at the point of expanding the custom column I've created.

One of the errors is related to the project not having a site sitting behind it I assume, with the error showing as "We cannot convert the value null to type Text".

The second error is with a project that does have a site sitting behind it, one that was used in early testing but does not have the custom issue fields I have created. The error returned is The field or property xxx does not exist. I've tried using the column filters to remove these projects from the reports but I then get a Syntax error.

My main aim right now is to not have these projects included in the report. It is unlikely that all of the old projects will have a project site because they are completed projects, so ideally I'd like to remove them from the query results. It is also unlikely that the project that has the older site will be changed as it has also finished, so again would like to remove it from the query results.

Can anyone point me in the right direction of what I need to do? Apologies if the information provided is really vague as well. As mentioned, this isn't my skillset but I'm struggling through 🙂 Thanks.

Edit: I have started again from scratch and don't seem to be getting the second error now. So now I just get a Syntax error when I try to filter results that don't have a project site (null in the ProjectWorkspaceInternalURL column) or the original project that has the older site.

[Anonymous]

Not sure why the last message was marked as spam, I'll try again removing the link.

Hi,

Apologies as this is probably really simple, but I'm definitely not technical. I have a Project Web App site with over 100 projects. We've been using it for some time and are now expanding our use of the functionality. The majority of these 100 projects does not have a SharePoint site sitting behind it and this is something we're looking into using from now on, mainly to make use of the risk and issue functionality. I've been following the guide on Paul Mathers blog on how to report on the custom fields I have created for my Issues list. I am however stuck at the point of expanding the custom column I've created.

One of the errors is related to the project not having a site sitting behind it I assume, with the error showing as "We cannot convert the value null to type Text".

The second error is with a project that does have a site sitting behind it, one that was used in early testing but does not have the custom issue fields I have created. The error returned is The field or property xxx does not exist. I've tried using the column filters to remove these projects from the reports but I then get a Syntax error.

My main aim right now is to not have these projects included in the report. It is unlikely that all of the old projects will have a project site because they are completed projects, so ideally I'd like to remove them from the query results. It is also unlikely that the project that has the older site will be changed as it has also finished, so again would like to remove it from the query results.

Can anyone point me in the right direction of what I need to do? Apologies if the information provided is really vague as well. As mentioned, this isn't my skillset but I'm struggling through.

Thanks.

[v-diye-msft]

Hi @Anonymous ,

For the first error, please kindly remove the blank rows.

For the second error, did you change the column name? the error will happen if you changed the column name. you can refer to the similar post here: https://community.powerbi.com/t5/Power-Query/Issues-connecting-to-sharepoint/m-p/84597

For the Third one, did you try to disable the option below?

[Anonymous]

Hi @v-diye-msft 

Thanks for your response.

"For the first error, please kindly remove the blank rows."

This is the problem, I'm not sure how. Whilst still in the query editor, I've tried filtering out the results with null entries for the workspace Url by using the filter option in the column  but that returns a syntax error. I assume it can be done in the advanced editor but I don't know the code to do that with how this query is structured.

"For the second error, did you change the column name? the error will happen if you changed the column name. you can refer to the similar post here: https://community.powerbi.com/t5/Power-Query/Issues-connecting-to-sharepoint/m-p/84597"

I don't believe I have changed any column names, but have added some. The project site that originally existed and is being returned in the results does not contain all of the columns that I've added since.

Edit: I have started again from scratch and don't seem to be getting the second error now. So now I just get a Syntax error when I try to filter results that don't have a project site (null in the ProjectWorkspaceInternalURL column) or the original project that has the older site.

[Anonymous · Accepted solution]

I've figured out what I needed to do. For those not technical like me and needing help with similar.

Once completing your query in the editor, use the remove rows functionality and you'll see an option to remove errors. This will then allow you to expand the contents of the column.