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:
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:
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:
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:
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:
Now only thing left to do is extracting the values:
As a final result I will get an overview of the item count in all the lists:
