tnhurst57's avatar
tnhurst57
New Member
2 years ago
Status:
Needs Info

Power BI Power Automate button, calling flow which performs DAX query not returning all columns.

 

With a Power Automate flow triggered from a POwer BI screen, I issue a Power BI Query, get the results and write to a CSV File.

An example is: 

Power BI: Power Automate button executing flow which executes DAX query on Power BI dataset - not returning all columns.

 

Sample:

Evaluate
FILTER(Master,[Serial Number] IN {"621927000017","621927000018","621927000021","621927000022","721927000015","721927000016","721927000017","721927000018","721927000019","721927000020"}
&& [End Customer NAGP] = "24/7 Inc.")

Notice that I dont pass a list of columns since I want all columns.

When I execute this, it is not writing some columns. It should not be size as this only produces about 10 rows (250 columns).

When I execute this with DAX Studio, it works perfectly.

Also, this does not happen with every instance of the flow - but always happens on the same query instance.

APIS and Embedding

2 Comments

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi tnhurst57 

    Can you provide the exact steps to set up automate? Also ,you mentioned that it always happens on the same query instance , is this query this one you mentioned above?

     

    Best Regards,
    Community Support Team _ Ailsa Tao

     

  • tnhurst57's avatar
    tnhurst57
    New Member
    2 years ago

    So the query I provided has no columns specified as I want all columns. When I do the Power BI Query, I first create the query statement with Set Variable and use the variable as the Query statement in the Power BI line. To test, I went back into the flow after it executed, copied the statement directly out of the flow Input section and paste it into Dax Studio. With Power BI, I am missing columns. With DAX Studio all columns are retrieved.

    Here is where I setup the variable and execute the Power BI Query:

    {
    "inputs": {
    "name": "MQuery",
    "value": "Evaluate\nFILTER(Master,[Serial Number] IN {@{variables('SerialQuery')}}\n && [End Customer NAGP] = \"@{items('Create_Master_Query')?['End Customer NAGP']}\")"
    },
    "metadata": {
    "operationMetadataId": "a9b06e75-b960-4bbb-ace0-8d34716ec38f"
    }
    }
    {
    "inputs": {
    "host": {
    "connectionName": "shared_powerbi",
    "operationId": "ExecuteDatasetQuery",
    "apiId": "/providers/Microsoft.PowerApps/apis/shared_powerbi"
    },
    "parameters": {
    "groupid": "b0462676-d6bc-4217-b1f6-0b44ea160ea7",
    "datasetid": "bf3449bc-2572-48a0-ab93-d9f4968d5c30",
    "specification/query": "@variables('MQuery')",
    "specification/serializerSettings/includeNulls": false
    },
    "authentication": {
    "type": "Raw",
    "value": "@json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$ConnectionKey']"
    }
    },
    "metadata": {
    "operationMetadataId": "d9cfc537-401c-4a76-abf5-7ccecabdaca7"
    }
    }

Completed Ideas