How do I specify ProjectId in SQL statement in Power BI Desktop when calling CustomFields view in Asana through the CData connector?

Question

I use CData Connect Cloud. I have a connection to my Asana account. Here I can access Stored Procedures, Tables and Views.

Accessing the tables is not a problem. It load a preview, I open it up in Tranform, do my adjustments, and load the data into my Power BI Desktop.

But when I try to access the views, it usually requires to specify some additional fields before loading the preview. In my case I try to access the CustomField view in the Asana connector from Power BI Desktop, but can't find out how.

I Get this: QUERY_FAILED: The query failed with the following error: [500] Could not execute the specified command: At least PROJECTID should be specified in this SQL statement.

Clicking on Transform and then Advanced Editor, I get the following code:

let
    Source = CDataConnectCloud.Contents(),
    AsanaRune = Source{[Key="AsanaRune"]}[Data],
    AsanaRune.Asana = AsanaRune{[Key="AsanaRune.Asana"]}[Data],
    Views = AsanaRune.Asana{[Key="Views"]}[Data],
    AsanaRune.Asana.CustomFields = Views{[Key="AsanaRune.Asana.CustomFields"]}[Data]
in
    AsanaRune.Asana.CustomFields

How can I select all the fields in the CustomFields view, but specify the ProjectId.

I CData's pages it says:

For example, the following queries are processed server side: SELECT * FROM CustomFields WHERE ProjectId = '1126938691750986'

, but I can't find out how to do this.

Answer 1

You can still Transform the data from CustomFields and then modify the script in the Advanced Editor, like so:

    let
        Source = CDataConnectCloud.Contents(),
        AsanaRune = Source{[Key="AsanaRune"]}[Data],
        AsanaRune.Asana = AsanaRune{[Key="AsanaRune.Asana"]}[Data],
        Views = AsanaRune.Asana{[Key="Views"]}[Data],
        AsanaRune.Asana.CustomFields = Views{[Key="AsanaRune.Asana.CustomFields"]}          [Data],
        #"Filtered Rows" = Table.SelectRows(AsanaRune.Asana.CustomFields, each [ProjectId] = "1126938691750986")
    in
        #"Filtered Rows"