none
Expression.Error: We cannot apply field access to the type List. RRS feed

  • Question

  • Hi,

    I am struggling to solve the below issue when building the query in Power BI:

    let
        Projects = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa/_api/ProjectData/Projects()?$Filter=ProjectType ne 7 and substringof('template', ProjectName) ne true",
                                [
                                    Headers = [ #"Accept" = "application/json" ]
                                ])),
            ProjectsList = Projects[value],
        #"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Projects Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", {"ProjectName"}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Projects Column1"),
        #"Modified Type" = Table.TransformColumnTypes(#"Removed Errors",{{"ProjectName", type nullable text}}),
    
        RisksColumn = (#"Modified Type") =>
    
         let
            Risks = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa", 
                                [
                                    RelativePath=#"Modified Type"[ProjectName]&"/_api/web/lists/GetByTitle('Risks')/Items()"
                                ])),
            valueRis = Risks[success]
        in
            valueRis,
    
        Output = Table.AddColumn(#"Modified Type", "Risks Column", each RisksColumn(#"Modified Type"[ProjectName])),
    in
        Output

    I get the error:

    Expression.Error: We cannot apply field access to the type List.
    Details:
        Value=List
        Key=ProjectName

    Please, any advice will be much appreciated


    • Edited by Ximet Tuesday, June 11, 2019 7:58 PM
    Tuesday, June 11, 2019 7:57 PM

Answers

  • Got it!!

    let
        Projects = Json.Document(Web.Contents("https://***.sharepoint.com/sites/pwa/", 
                                [
                                    Headers=[ #"Accept" ="application/json" ],
                                    RelativePath = "_api/ProjectData/Projects()?$Filter=ProjectType ne 7 and substringof('template', ProjectName) ne true"
                                ])),
            ProjectsList = Projects[value],
        #"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Projects Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", {"ProjectName"}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Projects Column1"),
        #"Modified Type" = Table.TransformColumnTypes(#"Removed Errors",{{"ProjectName", type nullable text}}),
    
        RisksColumn = (ProjectName) =>
         let
            Risks = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa/", 
                                [
                                    Headers = [ #"Accept" = "application/json;odata=verbose" ],    
                                    RelativePath = Text.From(ProjectName)&"/_api/web/lists/GetByTitle('Risks')/Items()"
                                ]))
        in
            Risks,
    
        Output = Table.AddColumn(#"Modified Type", "Risks Column", each RisksColumn([ProjectName])),
        #"Expanded Risks Column" = Table.ExpandRecordColumn(Output, "Risks Column", {"d"}, {"Risks Column.d"}),
        #"Expanded Risks Column.d" = Table.ExpandRecordColumn(#"Expanded Risks Column", "Risks Column.d", {"results"}, {"Risks Column.d.results"}),
        #"Expanded Risks Column.d.results" = Table.ExpandListColumn(#"Expanded Risks Column.d", "Risks Column.d.results"),
        #"Expanded Risks Column.d.results1" = Table.ExpandRecordColumn(#"Expanded Risks Column.d.results", "Risks Column.d.results", {"Title", "AssignedToId", "Status", "Mitigation_x0020_plan", "Effect_x002f_s", "Impact0", "Likelihood", "Related_x0020_Issue_x002f_sId", "RIskID", "Risk_x0020_Level"}, {"Risks Column.d.results.Title", "Risks Column.d.results.AssignedToId", "Risks Column.d.results.Status", "Risks Column.d.results.Mitigation_x0020_plan", "Risks Column.d.results.Effect_x002f_s", "Risks Column.d.results.Impact0", "Risks Column.d.results.Likelihood", "Risks Column.d.results.Related_x0020_Issue_x002f_sId", "Risks Column.d.results.RIskID", "Risks Column.d.results.Risk_x0020_Level"})
    in
        #"Expanded Risks Column.d.results1"

    Just added Headers = [ #"Accept" = "application/json;odata=verbose" ] to the second Relative Path. Now I got Records:

    And after some transformations all the needed data about each Risk in each Risk list in each Project Site!

    Next challenge is that Power BI Service says that credentials are invalid:

    Then Editing credentials and using OAuth2 and my organizational credentials with permissions:



    • Edited by Ximet Saturday, June 15, 2019 7:53 AM
    • Marked as answer by Ximet Saturday, June 22, 2019 6:53 PM
    Saturday, June 15, 2019 7:52 AM
  • Well, thread closed, Microsoft has just confirmed this query is not supported in Power BI Service.

    "

    The connection string that we construct in M will not be identified in service at the time of static analysis. Meaning, we try to look into M and pick the connection strings (datasource uri's). But the one's we construct will come to life only when we actually execute that particular M script which happens only during execution not when we perform static analysis. I do not think refresh will be successful in this case.

    "

    Thank you all!

     
    • Proposed as answer by Ian Bruckner Thursday, June 20, 2019 8:17 PM
    • Marked as answer by Ximet Saturday, June 22, 2019 6:53 PM
    Thursday, June 20, 2019 7:29 PM

All replies

  • Hi,

    Input for RisksColumn function is table, but on Output step you use list instead of table:

     RisksColumn(#"Modified Type"[ProjectName])
    Wednesday, June 12, 2019 11:11 AM
  • Good catch!

    Thanks Aleksei, using table or list in both Input and Output the error now is:

    DataFormat.Error: We found extra characters at the end of JSON input.
    Details:
        Value=
        Position=0


    let
        Projects = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa/_api/ProjectData/Projects()?$Filter=ProjectType ne 7 and substringof('template', ProjectName) ne true",
                                [
                                    Headers = [ #"Accept" = "application/json" ]
                                ])),
            ProjectsList = Projects[value],
        #"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Projects Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", {"ProjectName"}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Projects Column1"),
    
        RisksColumn = (ProjectName) =>
    
         let
            Risks = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa", 
                                [
                                    RelativePath=ProjectName&"/_api/web/lists/GetByTitle('Risks')/Items()"
                                ])),
            valueRis = Risks[success]
        in
            valueRis,
    
        Output = Table.AddColumn(#"Removed Errors", "Risks Column", each RisksColumn([ProjectName]))
    in
        Output

    Thank you!

    Wednesday, June 12, 2019 1:36 PM
  • It's difficult for me judge about causes of the error without data. I may just trivially advice you to check your JSON input.
    Wednesday, June 12, 2019 11:03 PM
  • Sorry for being such a pain... 

    Tried with 

    let
            Risks = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa/ActualProjectSite/_api/web/lists/GetByTitle('Risks')/Items()"))
    in
            Risks

    And the error still the same:

    DataFormat.Error: We found extra characters at the end of JSON input.
    Details:
        Value=
        Position=0

    Is there other way to check this JSON input?

    Thanks!

    Thursday, June 13, 2019 7:05 AM
  • You may try to verify JSON format here:

    https://jsonformatter.curiousconcept.com/

    Thursday, June 13, 2019 9:50 AM
  • What's a sample expected value for the Risks Column?


    Ian

    Thursday, June 13, 2019 4:15 PM
  • Thanks Aleksei,

    we need O365 authentication to test it so jsonformatter does not work for this...

    Will keep trying!


    • Edited by Ximet Thursday, June 13, 2019 8:24 PM
    Thursday, June 13, 2019 8:23 PM
  • Hello Ian,

    thanks for joining the conversation!

    Not sure, I had always use OData.feed previously and switched to test with JSON because we needed to get Scheduled Refresh in Power BI Service and found Chris Webb post at:

    https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/

    But seems that something in the default JSON answer in Project / SharePoint Online is not ok.

    Thursday, June 13, 2019 8:29 PM
  • yea - no variables in the url for the odata string are allowed when the odata query is run in the powerbi service (though annoyingly, they run when you refresh straight in powerbi desktop and excel/powerquery).

    In another circumstance I found that I didn't need the variable I was trying to use... it was part of a filter statement. PowerBI, it appears, is able to do some rollup... so the until you start doing transforms in your query, all the steps before that (such as filters [for sure] and selects [I think] )are intelligently rolled into a single URL for the odata feed... Check it out using fiddler. Moral of that story, I didn't end up needing to use Chris's method because I could supply a URL that didn't contain a the filter (it was a date filter for datetime.now() since that's not available in the odata version pwa runs) and just in the first step after that, apply the filter using the gui... using fiddler I confirmed that the current date was actually included as a $filter in the URL powerbi runs for the odata query.

    However - still curious to see a sample value you're aiming to see in the risks column.

    Also, if you go to that URL (https://***.sharepoint.com/sites/pwa/ActualProjectSite/_api/web/lists/GetByTitle('Risks')/Items())in internet explorer, do you get results? And do you have separate risk lists on each project site?

    If that's not blank but looks like an rss feed, follow these instructions: http://www.jaysmith.us/post/Turn-Off-Feed-Read-Preview-to-Show-XML-in-Internet-Explorer.aspx

    Going to any URL that you'd supply for an odata string in powerbi should be return xml in internet explorer so long as you're already logged into pwa in the browser.


    Ian

    Friday, June 14, 2019 1:07 PM
  • Thanks Ian!

    The URL returns the following XML:

    Items().txt

    Yeap… in Project Online, each Project Site contains a Risk list. We wanted to report on all Risks in the PWA Site Collection, for all Project Sites (we have custom columns not available in the default OData for Risks) and found the query cross approach described by Paul Mather. This works perfectly in Power BI Desktop but is not working for Power Bi Service when needed Scheduled Refreshes.

    Chris Webb replied to my question about the JSON error (We found extra characters at the end of JSON input) saying that it could be the web service call generating an error or malformed JSON, but not sure how can this happen, I have not much experience working with JSON.


    • Edited by Ximet Friday, June 14, 2019 7:40 PM
    Friday, June 14, 2019 7:36 PM
  • Try without the () at the end of items? I think it works without them, too.

    If that's not it, I think the only real next step you have is to install fiddler and see exactly what powerBI is sending when you refresh the query.

    googling, which I'm sure you've done, shows this doesn't seem to be uncommon... have you seen this entry? https://community.powerbi.com/t5/Desktop/We-found-extra-characters-at-the-end-of-JSON-input/m-p/389071/highlight/true#M177238

    And if that all fails... might be time to open a support ticket with microsoft - they're usually helpful.


    Ian

    Friday, June 14, 2019 7:52 PM
  • Thanks for providing such good hints! I will follow them and let you know how it goes Best, Xim
    Friday, June 14, 2019 8:16 PM
  • Got it!!

    let
        Projects = Json.Document(Web.Contents("https://***.sharepoint.com/sites/pwa/", 
                                [
                                    Headers=[ #"Accept" ="application/json" ],
                                    RelativePath = "_api/ProjectData/Projects()?$Filter=ProjectType ne 7 and substringof('template', ProjectName) ne true"
                                ])),
            ProjectsList = Projects[value],
        #"Projects List to Table" = Table.FromList(ProjectsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Expanded Projects Column1" = Table.ExpandRecordColumn(#"Projects List to Table", "Column1", {"ProjectName"}),
        #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Projects Column1"),
        #"Modified Type" = Table.TransformColumnTypes(#"Removed Errors",{{"ProjectName", type nullable text}}),
    
        RisksColumn = (ProjectName) =>
         let
            Risks = Json.Document(
                        Web.Contents("https://***.sharepoint.com/sites/pwa/", 
                                [
                                    Headers = [ #"Accept" = "application/json;odata=verbose" ],    
                                    RelativePath = Text.From(ProjectName)&"/_api/web/lists/GetByTitle('Risks')/Items()"
                                ]))
        in
            Risks,
    
        Output = Table.AddColumn(#"Modified Type", "Risks Column", each RisksColumn([ProjectName])),
        #"Expanded Risks Column" = Table.ExpandRecordColumn(Output, "Risks Column", {"d"}, {"Risks Column.d"}),
        #"Expanded Risks Column.d" = Table.ExpandRecordColumn(#"Expanded Risks Column", "Risks Column.d", {"results"}, {"Risks Column.d.results"}),
        #"Expanded Risks Column.d.results" = Table.ExpandListColumn(#"Expanded Risks Column.d", "Risks Column.d.results"),
        #"Expanded Risks Column.d.results1" = Table.ExpandRecordColumn(#"Expanded Risks Column.d.results", "Risks Column.d.results", {"Title", "AssignedToId", "Status", "Mitigation_x0020_plan", "Effect_x002f_s", "Impact0", "Likelihood", "Related_x0020_Issue_x002f_sId", "RIskID", "Risk_x0020_Level"}, {"Risks Column.d.results.Title", "Risks Column.d.results.AssignedToId", "Risks Column.d.results.Status", "Risks Column.d.results.Mitigation_x0020_plan", "Risks Column.d.results.Effect_x002f_s", "Risks Column.d.results.Impact0", "Risks Column.d.results.Likelihood", "Risks Column.d.results.Related_x0020_Issue_x002f_sId", "Risks Column.d.results.RIskID", "Risks Column.d.results.Risk_x0020_Level"})
    in
        #"Expanded Risks Column.d.results1"

    Just added Headers = [ #"Accept" = "application/json;odata=verbose" ] to the second Relative Path. Now I got Records:

    And after some transformations all the needed data about each Risk in each Risk list in each Project Site!

    Next challenge is that Power BI Service says that credentials are invalid:

    Then Editing credentials and using OAuth2 and my organizational credentials with permissions:



    • Edited by Ximet Saturday, June 15, 2019 7:53 AM
    • Marked as answer by Ximet Saturday, June 22, 2019 6:53 PM
    Saturday, June 15, 2019 7:52 AM
  • Well, thread closed, Microsoft has just confirmed this query is not supported in Power BI Service.

    "

    The connection string that we construct in M will not be identified in service at the time of static analysis. Meaning, we try to look into M and pick the connection strings (datasource uri's). But the one's we construct will come to life only when we actually execute that particular M script which happens only during execution not when we perform static analysis. I do not think refresh will be successful in this case.

    "

    Thank you all!

     
    • Proposed as answer by Ian Bruckner Thursday, June 20, 2019 8:17 PM
    • Marked as answer by Ximet Saturday, June 22, 2019 6:53 PM
    Thursday, June 20, 2019 7:29 PM
  • Yea - that's what I ran into. Thankfully in my case, I was able to utilize the system's native (and undocumented?) "query folding" to efficiently apply the filter statement I was trying to craft dynamically for the odata URL. Since all your risk lists are at different URLs, I don't think anything's going to help you other than revisiting that structure, if it's even possible, or making some complicated 365 flow to make a new list that copies and updates based on changes to the projects' lists. I haven't spent much time learning how risks/issues work in project online, especially because it seemed that you couldn't natively turn a risk into an issue.

    Ian

    Thursday, June 20, 2019 8:24 PM
  • Hello Ian,

    Project Online PWA is basically a Site Collection that creates sites for each Project and then connects some project PDPs, Schedule, Workflow pages to each SharePoint Site (Project Site). Then each Project Site has its own SharePoint lists for Risks and Issues. I do not see that revisiting the PWA structure is an easy approach or even if it is really allowed here. The flow approach looks good, though.

    Yeap, turning a Risk into an Issue still not possible, only workaround I found is add a custom column that looks into the Issues list and links a Risk to an Issue, then have one Risk status called Materialized...

    In any case, thanks a lot for helping out!


    • Edited by Ximet Saturday, June 22, 2019 6:54 PM
    Saturday, June 22, 2019 6:52 PM