Pivot Table Connection to Tulip Table

Support, Troubleshooting, & Help
,
1

Is it possible to connect Excel to a Tulip Table as its datasource for pivot table analysis?

2

You should look into Excel’s PowerQuery and Web as input data source; then you will need to establish a link to your instance’s API to fetch the data. But be prepared for some trouble to get the right subset of data if you want to fetch more than what is Tulips current Table API query row limit.

3

Thanks, will check that out.

Is this the same 1000 record limit as for non-connector Tulip Table queries?

That wouldn’t work for one of our use cases as we generate about 1000 inspection records per weekday and the desire is to have 1, 3, 6, and 12 month analyses to compare metrics across sites, users, etc.

4

Last time I checked the API calls were still restricted to 100 at a time…

5

@sebme is correct - Table API has a “GET” limit of 100 records Limit of table records or columns - #13 by Beth

You could run this with a script to get 100, then get another 100, etc. - making multiple requests to get all data

Let me see if the Tulip team has any recs here!

6

@jmlowden one more Q for you - are you wanting to work in a pivot table due to the question you asked here Analysis to Show Percentage of Total by Comparison or some other reason?

Just want to make sure we understand what your end goal is!

7

Hey @jmlowden !

this library asset specifies “PowerBI” but it’s actually a generic Power Query function under the hood that can easily be used with Excel: https://tulip.co/library/videos/powerbi-desktop-integration

This power query function also enables iterating over the datasets (100 records at a time) to get the full table.

Keep me posted if this is helpful! we can reframe / rescope this PowerBI example to more fully explain that it can be used in Excel

8

Hello William,

this would be very interesting. It would be great if you could share the function code because its seems the template cannot be opened / imported from within Excel / PowerQuery.

9

@william_vanbuskirk … any chance you will share that PowerQuery function code you used in PowerBI?

10

Hi @sebme,

the file can be downloaded by pressing the Template button, it can also be accessed via the support article.

Both are accessible via the card in the top right of the page:
image

I hope this answers your question.
Seb

11

Thanks Seb. Unfortunately, this is what I did and it is a dead end because the template is for PowerBI and cannot be opened in Excel’s PowerQuery - or at least in our version of Excel in the current release cycle we are in. Hence it would be great if the underlying PowerQuery function code could be shared as well instead.

12

Here is the Power Query we use:

> = (TulipTableId as text, instance as text) as table=>
> let
> Query = #table( type table [#"table" = text], { {TulipTableId}}),
> Function = Table.AddColumn(Query, "Offset", Number.Round(Json.Document(Web.Contents("https://"&instance&"/api/v3/tables/"& TulipTableId &"/count"))/100)),
> ExpandedOffset =Table.ExpandListColumn(Function, "Offset"),
> NbLoop=Number.Round(Json.Document(Web.Contents("https://"&instance&"/api/v3/tables/"& TulipTableId &"/count"))/100),
> TableOffset=Table.FromList(List.Transform({0..NbLoop},each Number.ToText(_*100))),
> TableOffset1=Table.RenameColumns(TableOffset,{{"Column1", "Offset"}}),
> TulipTable= Table.AddColumn(TableOffset1, "Records", each Table.FromRecords(Json.Document(Web.Contents("https://"&instance&"/api/v3/tables/" & TulipTableId & "/records?limit=100&offset="& [Offset] &"&sortBy=_sequenceNumber&sortDir=asc"))))
> 
> in
> TulipTable

For reference to others, here are the steps to use this query in excel:

  1. Open the Power Query Editor (Data > Get Data > Launch Power Query Editor…)

    image
    image1193Ă—996 53.5 KB

  2. In the editor, create a new query (New Source > Other Source > Blank Query)

    image
    image2375Ă—1324 119 KB

  3. Paste your query:

    image
    image2363Ă—1296 171 KB

  4. Review the query “Tulip Query” and replace Tulip TableID with desired table ID and instance with instance url (e.g., company.tulip.co)

  5. Click “Invoke” and enter API credentials (Use “Basic” Authentication)

  6. Clarify privacy level when prompted (Default is organization for most use cases)

  7. Click on the Split Arrows to the right of the records column

  8. Modify table as needed; click “Close and Apply”

1 Like
13

I am not able to format the query so that it can be easily be copied and pasted… quotes end up being the bad format and I need to do a replace all in notepad++…

So I created an excel spreadsheet similar to the power BI file in the library for people to use. You can download it from here