Posts in Category ‘Accounting’

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”:

QuickBooks Time Activity Navigation Pane

Choose “Entity” and a window will pop up where you enter “select * from TimeActivity” into the first field (“query”):

QuickBooks Function Dialogue

Click invoke and you might be prompted with the sign-in-dialogue. Sign in and continue will return a table like this:

QuickBooks Time Activity Result Table with Records

Click the expand-arrows (1) -> disable prefixes (2) -> Load more (3) -> Select All Columns -> OK -> dance the happy dance:

QuickBooks Results Time Activity

Employee data in a record that you can expand as you like:

QuickBooks Time Activity Employee data

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:

QuickBooks Hours

Bonus

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.

Enjoy & stay queryious 😉

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 😉

Why Power BI isn’t suitable for Financial Statements (… currently … hopefully …)

!! This is a clickbait post to get your vote for some missing features in Power BI !!

Although this might not be what the inventors of Power BI had in mind, large lots of folks are trying to create classical financial statements in it. And putting aside the afford that might go into getting the numbers right, there is still a major drawback to swallow:

Read more

Create list of accounts from NAV account schedules or COA totaling syntax

In this blogpost I’ll show you how to create a list of account numbers from the totalling syntax that you find in Dynamics NAV account schedules or chart of accounts for example:

1000..1090|1500|2000..2020

This string shall be transformed into a “real” list of account numbers in the query editor that can be used to select all accounts within those ranges. 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

Stretching and Compressing Time Series with Power Query and Power BI

The good folks from SumProduct have published a challenge for Excel that I find worth doing in Power Query instead:

The Challenge

Stretching or compressing a forecast so that the proportion of the original series will be maintained:

The Function

This is not so easy mathematically (as the solution-blogpost explains in detail), so the code for the function isn’t short as well:

Read more