getallTulipTableRecords in Excel

[mathieu.rey]

Hi,

you can get all records from tulip table thanks to BOT.

For this you need to use powerQuery functionnality on Excel.
You can easily copy the function in advanced editor (see below)

(TulipTableId as text) as table=>

let
Query = table( type table [#“TulipTableId” = text], { {TulipTableId}}),
Function = Table.AddColumn(Query, “Offset”, Number.Round(Json.Document(Web.Contents(“https://[server]/api/v3/tables/”& TulipTableId &“/count”))/100)),
ExpandedOffset =Table.ExpandListColumn(Function, “Offset”),
NbLoop=Number.Round(Json.Document(Web.Contents(“https://[server]/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://[server]/api/v3/tables/” & TulipTableId & “/records?limit=100&offset=”& [Offset] &“&sortBy=_sequenceNumber&sortDir=asc”))))

in
TulipTable

Do not forget to change the variable [server] by your server name.

The function set, you can get all records from your tulip table by invoke the function and copy/paste your Tulip Table Id.

[oviland]

Hi,
Thank you for this great query.
I have tried to implement this solution but I’m not able to connect to Tulip table.
It stops with the authorization.

It would be great to get a step by step guide on how to connect within Power BI Desktop.

(I do have a Bot for Tulip tables)

Thanks.

[gio]

hello @oviland, I agree it is a great query - thanks @mathieu.rey for sharing!!

@oviland

It stops with the authorization

can you share what the error message you’re seeing is with the authorization??

also, just to confirm, do you have the necessary Scopes enabled on the Bot??

[oviland]

Thank you for your response.

I try to connnect using the data source “Web” in Power BI and then choosed “Web API” for credentials. (This might be were it went wrong)

The error message I get is this:
A web API key can only be specified when a web API key name is provided.

For the Web API there is only room for the key!

[oviland]

Just a quick update.
A changed it to Basic instead of Web API and inserted key as user name and secret as password.
It connects but now I get this error:
Formula.Firewall: Query ‘Invoked Function’ (step ‘Source’) is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

[gio]

OK, thanks for the update @oviland.

@mathieu.rey are you also using Basic credentials to connect the Bot from Power BI?? if so, is there anything that you think we need to keep an eye on?? thanks!!

[oviland]

I did a change in the privacy settings, and now it connects.
Just need to figure out how to extract the data from all the records!

[GuyW]

Hi @mathieu.rey, thank you for this post, really useful.

My PowerQuery is showing the issue below, any ideas?
Please could you reiterate what you mean by “The function set, you can get all records from your tulip table by invoke the function and copy/paste your Tulip Table Id.”

Many thanks,
G

[mathieu.rey]

Hello
you should use basic and not web api.
Take care by using the right user and pwd.

as you shown @oviland you could none as level of privacy at it shows below

[mathieu.rey]

hello @GuyW the tuliptableId it is just the id of your table.
i have add this value as input parameter. By default, it could be a fixed value if you know the id.

if you want to use it as input parameter you need to add this extra first row

At the end it shoud give you the possibility to enter any kind of value on the parameter “TulipTableId” as it is shown below

[GuyW]

Thank you very much @mathieu.rey, that was the issue. My mistake.

For others trying this process, I also found that when copying the code @mathieu.rey kindly shared above, some of the " speech mark symbols didn’t copy properly. Once they were replaced manually the query worked.

[GuyW]

Hi @mathieu.rey .

Now I have the query set up correctly, I enter by TulipTableId, but I get an error “Expression.Error: Access to the resource is forbidden”.

Do you know why this might be ?

Many thanks,
G

[GuyW]

This issue was solved by using Tulip BOT login details in the credential as described by @oviland above. As he says, you have to fiddle with the security settings abit. These are found under ‘File’, ‘Options and Setting’ and then ‘Data Source Settings’

[mathieu.rey]

here the configuration explanation
and where to configure