CODEWITHSUNDEEP
powerbipowerbi-desktoppowerbi-custom-visuals
Francesco Mantovani
Francesco Mantovani

Reputation: 12367

Export data to CSV from URL

I need to export data from Power BI to Google Sheets. I noticed that Google Sheet proposes IMPORTDATA which works like:

IMPORTDATA("https://www2.census.gov/programs-surveys/popest/datasets/2010-2019/national/totals/nst-est2019-popchg2010_2019.csv")

Can a Power BI report or a table inside a report to be exported as a CSV in a URL? I see that the official documentation offers something like this with even the FileFormat but I cannot tell if this can be done. The "Try it" button is not very user friendly:

enter image description here

Am I missing something that I have to add?

Maybe in Postman?

Upvotes: 2

Views: 209

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Let's suppose that in your visual you are querying data from columns Column1, Column2 and Column3 in Table 1 ,this will be your query :

EVALUATE
SUMMARIZECOLUMNS(
    'Table'[Column1],
    'Table'[Column2],
    'Table'[Column3]
)

You can use the ExecuteQueries API to run this query :

POST https://api.powerbi.com/v1.0/myorg/datasets/{datasetId}/executeQueries
Authorization: Bearer {accessToken}
Content-Type: application/json

{
    "queries": [
        {
            "query": "EVALUATE SUMMARIZECOLUMNS('Table'[Column1], 'Table'[Column2], 'Table'[Column3])"
        }
    ],
    "serializerSettings": {
        "includeNulls": "false",
        "format": "json"
    }
}

After you receive the JSON response from the API, you'll need to parse it you can use Python for example.

Perquisites to use the API :

  • Register an application in Azure AD to interact with the Power BI API
  • Get the OAuth2 token using client or user credentials

To add a flavor of "automating", you can use Power Automate to trigger the Power BI API, export the data, and then store it in a location like OneDrive or SharePoint.

Then you can create a public link to the CSV file, but you need to ensure that this link is accessible without requiring authentication, which may not be compliant with your organization policies.

Upvotes: 1

Related Questions