Power BI, Excel & GP – Data Access

This is the third post in the series and I am going to dedicate this post to how to access GP data in terms of describing some of the options and factors to consider. Very little of this will have “how to” information about Power Query, but I will continue that in the next post.

The previous posts in the series so far are:

Continue reading “Power BI, Excel & GP – Data Access”

Power BI, Excel & GP – Starting from scratch

The first part of my series around Power BI, Excel & Dynamics GP begins with some of the basics, and a way to start learning Power Query, literally starting from scratch.

From my experience, an audience that is often overlooked are users who want to learn more or improve their skills but doesn’t know where to start, and everything they find starts at a point they don’t understand or can’t get to. For example, this post starts without getting into directly accessing data from SQL, which admittedly adds a level of complexity that some users have trouble getting past. Most of the rest of this series I will be accessing SQL data as that’s more efficient, but in this one, I’m using SmartList exports to make the learning more accessible.

This is post #1 of the series, aside from the introduction post here. In this post, I’m using the context of Dynamics GP to make a “real world” example with Accounts Payable data.

Continue reading “Power BI, Excel & GP – Starting from scratch”

Power BI, Excel & Dynamics GP

Soon I will be starting a new series of posts around Dynamics GP and how to get data out of it with Power BI or Excel via Power Query. There is no shortage of reporting options available for Dynamics GP customers, with many ISV (3rd party) products out there to augment some out of the box options (Report Writer, SmartLists). However, as with accounting departments everywhere, there is a heavy reliance on Excel as the “go to” tool for reporting and data analysis.

There are a lot of customers who do a lot of “export this SmartList to Excel and then do X” to get data ready. I’ve found many users do things in Excel that are repeatable yet are unaware of Power Query and more advanced ETL (Extract, Transform, & Load) techniques to automate some of the work. The other part of the coin here is there are many times where users or management want to get dashboards and higher level metrics on how they are doing, not just data dumps to wade through. I hope to cover a bit of both here.

Continue reading “Power BI, Excel & Dynamics GP”

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!):

  1. Connecting to Harvest in Power Query
  2. Pagination in Power Query (Part 1 – dealing with a known number of pages)
  3. Connecting to ClickUp in Power Query
  4. Pagination in Power Query (Part 2 – iterating over an unknown number of pages)
Continue reading “Pagination in Power Query (follow up)”

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.

Continue reading “Clear Recent Items list 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:

  1. Connecting to Harvest in Power Query
  2. Pagination in Power Query (Part 1 – dealing with a known number of pages)
  3. Connecting to ClickUp in Power Query

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
Continue reading “Pagination in Power Query (Part 2)”

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:

  1. Connecting to Harvest in Power Query
  2. Pagination in Power Query (Part 1 – dealing with a known number of pages)

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.

Continue reading “Connecting to ClickUp in Power Query”

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.

Continue reading “Power BI number formatting tips”

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:

  1. Connecting to Harvest in Power Query
  2. 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
Continue reading “Pagination in Power Query (Part 1)”

Posts navigation

1 2
Scroll to top