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.

Wondering how to apply the function? Please check out this video on how to export data from Power BI to csv using the Python script:

Enjoy & stay queryious 😉

Comments (19) Write a comment

  1. Pingback: Combining M and Python To Export Power BI Data To CSVs – Curated SQL

  2. Thank you for this awesome function to write CSV-s! I just want to add that it took some time for me to figure out that the input to the path parameter should look like this: C:\Data\filename.csv
    also I think it can’t work with Anaconda because pandas does not have the right to call numpy
    – “Missing required dependencies {0}”.format(missing_dependencies) –
    so I had to install Python 3.7 and install pandas. Sincerely: Bertalan

    Reply

  3. Pingback: Export large amount of data from Power BI desktop visuals –

  4. I tried this calling a View on my azure sql server but didn’t get through. It gave me: “Formula.Firewall: Query ‘Invoked Function’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”.

    So, I copy-pasted the View code into a DirectQuery in PBI but still gave the same error. I’m assuming it could be because my View source code has multiple sub-queries, unpivots, etc.

    Just wondering if these considerations were made during the design?

    Reply

    • I usually disable the security settings for those applications. Not sure how it works with DirectQuery though.

      Reply

  5. Whenever we try to put this function code above in our python script editor in power bi, we get the syntax error: ”’PythonScriptWrapper.PY” line 20
    let func =
    ^

    Does anybody know how to solve this?

    Thanks in advance!

    Reply

    • Thanks for your reply, however in the video they use the m-code for a blank query/table. In my situation we have a table that is a result from different powerquery’s and because of that we can’t select it as a ”SOURCE” when we need to fill in our parameters. Do you have any idea how to fix this?

      Reply

        • When we want to put in the m-python code in the advanced query editor it doesn’t work because the table we want to export is derived from a numerous of other queries. So when we use the m-python code we get a syntax error.

          Reply

          • If my understanding is correct, an intermediate table would be a good solution in that case:
            Create a table that consolidates what shall be exported “from numerous other queries” to one table before feeding it to the script.
            /Imke

  6. Thanks for replying! If i make an intermediate table using DAX then it isn’t possible to take that table to powerquery editor right?

    Reply

    • Yes, once you’re in DAX, there is no way back to Power Query.
      But if your table is not too large, you could run the Python-script from a visual instead (check out documentation about the current sampling figure !!). You have to use only Python in there. A very simple implementation without any parameters would look like so:
      import csv
      import pandas as pd
      path = r'C:/YourFilePathGoesHere/TestExport.csv'
      dataset.to_csv(path)

      Where “dataset” is referencing the fields that you’ve dragged into the visual.
      But again, sampling might occur!!

      Reply

  7. When we use this as a python visual it works perfectly in the desktop version but when in power bi service we get the error that it cant find our path location. Could you please upload a video or explain how we can get this working in the power bi service because we keep getting

    Reply

  8. I have a simple table in Power bi and i’m trying to export the data in it to excel using this code. I have kept the privacy setting to Public and still receive the error “Formula.Firewall: Query ‘Invoked Function’ (step ‘Source’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.” Could you please tell me how to resolve this?

    Reply

  9. Hello This function works very well for me but I have an issue I have in one column this text 12/5-1 that I want to keep as a text, but when opening the .csv this text is automatically convert into a date 12/05/2001 how to solve this issue

    Reply

  10. Hi…I am getting below error after pasting the code in Advanced editor:

    An error occurred in the ‘’ query. Expression.Error: The name ‘Python.Execute’ wasn’t recognized. Make sure it’s spelled correctly.

    Does someone knows how to solve this?

    Thanks in advance!

    Reply

Leave a Reply

Die Datenschutzbestimmungen finden Sie hier: / Please find the privacy policy here: https://wp.me/P6lgsG-Rz