Use Power Query in Excel to leverage the power of the Anaplan API

When working on our quality assurance items, we realized that one of the items that could go wrong in our regular data loads was that the lists loaded to our data hub could be empty. We had to figure out a way of monitoring this in a simple and straightforward manner. So, I decided to go for the combination of Excel and Anaplan API.

As we can read in the API documentation (Anaplan Integration API V2 Guide and Reference · Apiary), we can get a list of all the lists in Anaplan:

CommunityMember129720_0-1657006969280.png

The only issue is that this only gives you the lists, but not the metadata of these lists. Therefore, you would need to run this API call:

CommunityMember129720_1-1657006999084.png

But how to run this call for all the lists?

To get started I use the code from this article to do the authentication: How to Create Connection Between Anaplan and Power... - Anaplan Community

After that I got the list of all the lists with the query described above, also using Power Query:

CommunityMember129720_2-1657007046020.png

Now I only need to run the API call for all the items for which I need information. As an example of the possible items that can be retrieved, see below list:

id

name

hasSelectiveAccess

subsets

topLevelItem

productionData

managedBy

numberedList

useTopLevelAsPageDefault

itemCount

workflowEnabled

permittedItems

usedInAppliesTo

usedAsFormat

usedInFormula

In my example I wanted to get the itemCount, so in my query I need the following:

CommunityMember129720_0-1657007828582.png

You can see that I already created a function for it, so I can run it on every item in a table: = Table.AddColumn(IDTable, "Count", each fnItemCount([ID]))

Giving me this:

CommunityMember129720_4-1657007220052.png

Now only thing left to do is extracting the values:

CommunityMember129720_5-1657007243875.png

As  a final result I will get an overview of the item count in all the lists:

CommunityMember129720_6-1657007291136.png

The content in this article has not been evaluated for all Anaplan implementations and may not be recommended for your specific situation.
Please consult your internal administrators prior to applying any of the ideas or steps in this article.
Version history
Last update:
4 weeks ago
Updated by:
About the Author
Labels (1)