Jen Kuntz

2021 year in review

It’s hard to believe it’s the start of another year. 2020 felt like it lasted forever while 2021 was a blur. I think 2021 was “2020 one” and 2022 is “2020 two”, which is what this pandemic feels like at the moment, that 2020 has never actually ended. Nearly two years into this pandemic and people still don’t know how to wear a mask properly, but I digress…

Anyway, it’s always interesting re-reading the previous year or two’s posts to see where my head was at. Last year, I made no resolutions but did mention some things I thought might happen, so here’s how I fared.

Canadian Payroll 2021 Year End Update

In all the 2020 excitement, I realized I never posted about the 2020 Year End update. I think 2020 for me was all about getting through day to day things and I just forgot about this. Anyway, I’m back to blogging semi-regularly again and it’s that time of year again.

The CRA (Canada Revenue Agency) released their T4127 guide for January 1, 2022 very late this year (December 14th). Typically Microsoft (& other software vendors) get in late November to allow them adequate time to update their software for the new year tax changes.

Update 3: December 22, 2021 – The CanPay update is ready. See below.

ROE issue in Canadian Payroll

This post is about an issue that I found when testing the ROE (Record of Employment) functionality for a client in Dynamics GP. (Since “ROE” is a generic term, this post is specifically about a bug in the Dynamics GP Canadian Payroll module). I opened a case for it and it was quickly confirmed and written up as a quality issue (#91928 in case you need to add yourself or your customer to the list of affected clients).

Pagination in Power Query (follow up)

This post is an unintended follow up to my “mini-series” working through how I am connecting to Harvest and ClickUp APIs in Power Query. After writing up post #4 (iterating over an unknown number of pages), I decided to review the method I used there vs. the method in post #4 (List.Generate).

So, today’s post is really just describing another example of how to use List.Generate, this time the criteria is comparing where we are relative to the total number of pages returned. It was only after writing up the last post that I realized there is no reason not to approach both queries with the same way so I’m consistent in the underlying code. I find this approach to be much cleaner and easier to read than what I used in post #2, though both return the identical results!

The previous posts in this series were this (although today’s post was not intended to be part of the series!):

Clear Recent Items list in Power BI Desktop

Today’s tip I found completely by accident. In the Open Report window, I had a long list of previous Power BI reports in my “recently used” list, many of which I had long ago deleted or moved. I wanted to clear the list and could not find a way to do that in that screen. With Word or Excel, it’s easy to do in the equivalent screen so it was driving me a little nuts that I was not able to do it in Power BI Desktop.

Pagination in Power Query (Part 2)

This post is the 4th in a “mini-series” where I’m documenting how I’m working with some different APIs for custom reporting. Today’s post is about ClickUp, an app I use for managing projects, but like post #2 about Harvest, it’s less about ClickUp then it is about iterating over an unknown number of pages of results.

The specific nuance here is ClickUp returns a list every time, it does not return null when you hit a page with no records. There are several examples of List.Generate that are great, but they all appear to assume the condition for iterating is to stop once nulls are reached which was not applicable in my case.

Bottom line: if only everyone providing an API would tell you how many pages of data you have, pagination would be SO MUCH SIMPLER!

The previous posts in this series are:

In this post, I will describe the following items:

Creating a function that accepts a Page Number to loop through
Using List.Generate to loop through the pages similar to an If/Then or Do/While loop
Transforming the results into data
How to validate the results

Connecting to ClickUp in Power Query

This post is the 3rd in a “mini-series” where I’m documenting how I’m working with some different APIs for custom reporting. Today’s post is about ClickUp, an app I use for managing projects. Unlike the Harvest APIs I covered in post 1 & 2, ClickUp’s API is a little more challenging to deal with as there are no indicators about number of records or number of pages in the results so you need to do something a bit different than I wrote about previously. I’ll cover this in more detail in the next post.

The previous posts in this series are:

For this post, I will describe the following items:

Getting a ClickUp Personal Token
Authenticating with that in Power Query (Power BI or Excel)
Using “GET” method on the Spaces endpoint from the API

Some of the concepts I will cover should be similar regardless of which endpoint(s) you wish to use (at least in the context of the ClickUp API). The API documentation is the reference to what methods are available, what fields you get back, what data types they contain and what types of query parameters are available.

Power BI number formatting tips

Here are two tips that I use repeatedly when building reports in Power BI. “Repeatedly” is a bit of a stretch as I’m still a newbie with Power BI but this was remarkably hard to find so I’m blogging for my future self, when I forget. 🙂

For Tip #1, I love being able to format numbers in Excel easily with the click of a mouse button, but in Power BI, it’s not quite as straight forward. I like replacing 0’s with dashes, and I explain one way for you to do that.

For Tip #2, there are times where there is no data and the values show as blanks instead of showing a zero. This can occur on Card visuals where it shows “(blank)” and it can occur on matrix or other kinds of visuals too where “nothing” is just an empty space. If it’s a numeric or currency visual, I want to display the dash because that empty space is a zero value.

Pagination in Power Query (Part 1)

This post is the second in a “mini-series” where I’ll document how I’m working with some different APIs for custom reporting in Power BI and Excel. Today’s post is about Harvest again, but only in general terms. The example is their time_entries endpoint but what I’m really covering today I would term more generically as “iterating over a known number of pages” when calling an API.

There has been one post in the series so far plus this post:

Connecting to Harvest in Power Query
Pagination in Power Query (known number of pages) ** this post

In this post, I will describe the following items:

When to use pagination techniques
Updating the query to add in a pagination technique
Transforming the results into data

1 2 3 4 5 … 38 39 40

Author: Jen Kuntz