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 😉

Improved Text.SplitAny – function for Power BI and Power Query

Today I’ll present an adjustment to the Text.SplitAny – function in Power BI’s query editor or Power Query. The native function takes a string as an input and splits the text by every character that is contained in the string. This seems fairly unusual to me and I haven’t used that function very often.

Problem

But what I have come across fairly often is the requirement to split a string by a bunch of different (whole) strings (instead of single characters).

Solution

Read more

Return all occurrences of text between delimiters in Power BI and Power Query

The M-function Text.BetweenDelimiters does what it says and returns the text between 2 delimiters in the query editor of Power BI and Power Query. But irrespective of its optional arguments you can use to select specific occurrences, it will always only return one string found.

Problem

How to retrieve all occurrences of strings between in a text field like here for example: I’d like to get a list that contains “Python” and “R”:

“Meanwhile I prefer +Python!+ to +R!+”

Meaning all strings found between the delimiters “+” and “!+”.

Solution

This function will return the desired list:

Read more

Transform text with exceptions in Power BI and Power Query

Recently I picked up an interesting request to transform text with exceptions: Transform the words in a column to proper case, but keep certain keywords like in a defined list.

Problem: Transform text with exeptions

Say you have a list with specific terms that shouldn’t be proper cased like so:

And you want to proper case the following column:

Convert to proper case with exceptions

So I proper case each word that is not contained in the “KeepTable”, identify the elements in the “KeepTable” in a case insensitive way and transform them into the syntax that’s specified in the “KeepTable”.

Solution

Read more

Unravel cumulative totals to their initial elements in Power BI and Power Query

Recently I came across an interesting request where someone wanted to un-cumulate their quarterly YTD-figures (green) into their single quarters values (red) like so (“Unravel cumulative totals”):

Task

Retrieve every Quarters Amount from the Quarter To Date values (“YAmount”)

Method

To retrieve this value, one would have to start with the first value in the year. This is also the value of the first quarter, but for the 2nd quarter, one would have to deduct the value of the first quarter from the cumulative value of the 2nd quarter. So basically retrieving the previous cumulative row and deduct it from the current cumulative row. Do this for every row, unless it’s the start of the year or belongs to a different account code in this example:

Read more

Comparing Table.AlternateRows with List.Alternate in Power BI and Power Query

I must admit that I had more than one unsuccessful attempt to try to fully understand how the List.Alternate-function works. What helped me at the end, was the function Table.AlternateRows. It pretends to be similar to List.Alternate, but holds some surprises that I will uncover in this blogpost:

How Table.Alternate works

Say I have the table below and want to retrieve just the letters that appear in every 2nd row:

Table.Alternate – Remove every other row

I find the dialogue that appears very helpful and intuitive:

It clearly is a removal operation and here I want to remove the 1st row from my table (“1”), and just one at a time. Also want to keep just one row (“A”) before the next one is removed (“2”)  and so on.

In the formula bar, this step will be translated into this M-code:

Table.AlternateRows(Source,0,1,1)

If you would have expected it to be translated to: Table.AlternateRows(Source,1,1,1) instead, you might have forgotten that the M-language in Power Query starts to count at 0, so the first row to remove is expressed by the 0 here.

List.Alternate should work similar

So if my input is a list instead of a table like below, I should expect a similar result than the sample above if I tweak the code a bit, shouldn’t I?

List.Alternate – produces a different result

But hey: What’s wrong here? Not a single element has been removed from the list !!

So let’s have a look into the documentation:

List.Alternate – Function Documentation

and compare it with the Table.AlternateRows documentation:

Table.AlternateRows – Function Documentation

Hm – at least we have one match here: The “offset” parameter is included in both functions. But it is the first (number) parameter in the Table-function and is at the last position in the List-function. So let’s move it around then like this:

List.Alternate – same result with different parameter order

There we are 🙂

So the order of the function parameters is different here. Also the other parameter names are different and their description. I find them much easier to understand in the Table function and of course, the function dialogue there helps to understand what shall happen as well.

Enjoy & stay queryious 😉