Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Trouble with Odata query with relativepath in PowerQuery

Hello!

 

For a query I am currently using Odata with the relativepath function. I am trying to pass a date filter through the query but I'm having issues in Powerbi.

 

Essential I am replacing part of my query where="1=1", with where=" tijdstip+>+DATE+'2023-06-29'", 

This works no problem in the actual normal query...

(both of these work)

 

https://XXX/server/rest/services/XXX/FeatureServer/0/query?where=tijdstip+>+DATE+'2023-06-29'&outFields=*&f=json&token=XXX

 

 

https://XXX/server/rest/services/XXX/FeatureServer/0/query?where=1=1&outFields=*&f=json&token=XXX

 

 

 

...but as soon as I put them in my actualy query with relativepath the new one doesn't work.

(old: works)

    #"Added Custom - QUERY" = Table.AddColumn(#"Changed Type", "Custom.1", each Json.Document(Web.Contents("https://xxx/server",
    [
        RelativePath="rest/services/xxx/FeatureServer/0/query",
        Query=[
            where="1=1",
            outFields="*",
            resultOffset=[Column1],
            token=access_token1,
            f="json"
        ]
    ]
    ))),

 

 

(new: doesnt work)

    #"Added Custom - QUERY" = Table.AddColumn(#"Changed Type", "Custom.1", each Json.Document(Web.Contents("https://xxx/server",
    [
        RelativePath="rest/services/xxx/FeatureServer/0/query",
        Query=[
            where="tijdstip+>+DATE+'2023-06-29'",
            outFields="*",
            resultOffset=[Column1],
            token=access_token1,
            f="json"
        ]
    ]
    ))),

 

 

 

Does anyone know what I'm doing wrong?

 

Cheers,

Erik

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Found the issue.

    I had to replace all the + with space. 

     

       #"Added Custom - QUERY" = Table.AddColumn(#"Changed Type", "Custom.1", each Json.Document(Web.Contents("https://xxx/server",
        [
            RelativePath="rest/services/xxx/FeatureServer/0/query",
            Query=[
                where="tijdstip > DATE '2023-06-29'",
                outFields="*",
                resultOffset=[Column1],
                token=access_token1,
                f="json"
            ]
        ]
        ))),

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Found the issue.

    I had to replace all the + with space. 

     

       #"Added Custom - QUERY" = Table.AddColumn(#"Changed Type", "Custom.1", each Json.Document(Web.Contents("https://xxx/server",
        [
            RelativePath="rest/services/xxx/FeatureServer/0/query",
            Query=[
                where="tijdstip > DATE '2023-06-29'",
                outFields="*",
                resultOffset=[Column1],
                token=access_token1,
                f="json"
            ]
        ]
        ))),