Sometimes I need to retrieve the textual representation of a type in Power Query and I’m using a fairly verbose function that I’ve stolen ages ago (I believe it was here: https://ssbi-blog.de/technical-topics-english/power-query-editor-using-notepad/ ) :
Screenshot tutorial: Add a column with custom function code in Power Query
The following steps show how to create a new column in a table using existing custom function code. This works in Power BI as well as in Power Query in Excel:
1. Copy the function code
Writing data to GitHub using Power Query only
You shouldn’t do it …
Generally it’s a very bad idea to execute commands in Power Query that write out data to a source, as these commands might be executed multiple times (https://blog.crossjoin.co.uk/2018/10/07/power-query-write-data/)
… unless … maybe ?
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:
The video
Get full Time Activity data from QuickBooks into Power BI
Problem
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).
Solution
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”:
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.
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).