Today’s post is combining the topics of the previous two posts and moving into Power Query to connect to SQL directly instead of connecting to static, exported SmartLists or flat files.
The previous posts in the series are:
Tag: Excel
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:
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.
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!):
- Connecting to Harvest in Power Query
- Pagination in Power Query (Part 1 – dealing with a known number of pages)
- Connecting to ClickUp in Power Query
- Pagination in Power Query (Part 2 – iterating over an unknown number of pages)
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:
- Connecting to Harvest in Power Query
- Pagination in Power Query (Part 1 – dealing with a known number of pages)
- 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
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:
- Connecting to Harvest in Power Query
- 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”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
Connecting to Harvest in Power Query
Today’s post is the start of a “mini-series” where I’ll document how I’m working with some different APIs for custom reporting. Today’s post is about Harvest, an app I use for time tracking and billing. This series is aimed at users who may have similar requirements to mine, which was pulling data out of different application APIs for analysis in Power BI and/or Excel. In my limited experience, I’m finding significant differences in how to connect to them or how to paginate through them and I wanted to share my learning on this in case it helps others!
For this post, I will describe the following items:
- Getting a Harvest’s API v2 Personal Access Token
- Authenticating with that in Power Query (Power BI or Excel)
- Using “GET” method on the Time Entries 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 Harvest API). The API documentation is your friend here as it will be 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 Harvest in Power Query”Excel – where did my workbook go?
Today’s #TipTuesday is an odd one. A few months ago, a client had an issue with a specific Excel workbook they couldn’t “open”. Of course, the usual questions around “is there an error message?” etc. didn’t pan out. Typically it might be a corrupt file issue or something, but once I saw the issue on their computer, I realized what the problem was. It turns out someone accidentally “hid” the workbook and then saved it for the next unsuspecting user. To the user, it was akin to voodoo, something appears to “open” but nothing is visible.
Continue reading “Excel – where did my workbook go?”