Posts in Category ‘Python’

Export data from Power BI to csv using Python

In this blogpost I show you my M-Python-function that I use to export data from Power BI to csv files (Export Python).

Why Python?

I prefer it to R mostly because I don’t have to create the csv-file(names) in advance before I import data to it. This is particularly important for scenarios where I want to append data to an existing file. The key for this task is NOT to use the append-option that Python offers, because M-scripts will be executed multiple times and this would create a total mess in my file. Instead I create a new file with the context to append and use the Import-from-folder method instead to stitch all csvs back together. Therefore I have to dynamically create new filenames for each import. So when the M-Python-scripts are executed repetitively here, the newly created file will just be overwritten – which doesn’t do any harm.

Caveats

  • As with R, date-formats will not be recognized correctly when imported into the engines, so my function transforms all columns to text before passing the data frame to the Python-script.
  • Single numbers (like 10) will be converted to decimals (10.0) by the Python-engine. When you import this file back to Power BI and transform the column to number, this decimal place will disappear again. But if you want to use the data somewhere else, you should be aware of this.
  • Leading zeroes (“0001”) will be deleted (“1″). I haven’t found a way around this yet, apart from prefixing it with a string like a single quote (” ‘ “) and remove that when re-importing.

Function code

I’ve included some optional parameters, whose default values are this:

  • index: None, which means that no additional index-column will be created during export. Any value that you fill in here will make an index-column be created.
  • header: True, which means that by default the header-row will be created. Again, any value in my function parameter will make that header disappear.
  • quoting: csv.QUOTE_NONE, but you can use other options as well (see the docs: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_csv.html) .
  • chunksize: none, but you can use any number to chunk up the export process.

Also the slashes in your filenames will automatically be reverted from “/” to “\” so that you don’t have to take care of this manually.

At the time of writing these scripts can be refreshed in the service with the personal gateway, but not with the enterprise gateway yet.

Enjoy & stay queryious 😉