However, as with most good rules, there are exceptions. I leave it to you to decide whether my use case here is a valid candidate for it. It doesn’t execute the code twice, because I execute the query only from the query editor and none of the other queries is referencing its results. But please see for yourself – Writing data to GitHub using just Power Query:
In the video I show how I enrich my M-functions with metadata before loading it directly with Power Query into a new Gist on GitHub. Then I trigger an automatic update of my Function-library (M-extension). Therefore I have to switch to Power BI, because it currently not possible to run R- or Python-Scripts in Excel (which writes the .mez-file for me into the destination-folder).
Trading code for votes
The code I’m sharing today is the one that exports the M-code to GitHub. I’m going to share the full solution, as soon as the following features are implemented in Excel (like they are in Power Query for Power BI currently):
You can help this by upvoting the ideas of the links above. Actually, my guess is that we need around 1000 votes for these features to be considered. So please share this article with your colleagues and friends to make this happen.
As per the time of writing, the native QuickBooks connector in Power BI has some shortcomings for the Time Activity-data: It will not return employee details (so you will not know who did the hours) and it will not return hours (if they haven’t been entered by start- and end-date).
But fortunately the connector has 2 functions, who can return the full data that the QBO-API has to offer. At the end of the list in the navigation pane there are the functions “Entity” and “Report”:
Choose “Entity” and a window will pop up where you enter “select * from TimeActivity” into the first field (“query”):
Click invoke and you might be prompted with the sign-in-dialogue. Sign in and continue will return a table like this:
Click the expand-arrows (1) -> disable prefixes (2) -> Load more (3) -> Select All Columns -> OK -> dance the happy dance:
Employee data in a record that you can expand as you like:
And at the end of the table you’ll find the hours and minutes for the entries who didn’t use start- and end date:
According to the manual, you can also pass parameters to this function call. I’ve created a handy function with an optional parameter that allows you to pass in a date after which the time entries shall be returned. But you can use the function without this parameter as well and return all time entries by just adding open and close parenthesis like so: NameOfTheFunction()
This function also calculates the total duration from the 2 different capture methods.
In this blogpost I show you my M-Python-function that I use to export data from Power BI to csv files (Export 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.
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.
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.
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.
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).
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.
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 “!+”.