PowerBI

• Connect
• Read from Shooju

Connect

1. Create an API Key on the My Profile page (under your name on the top-right of the Shooju website) by using Create New API Key. In PowerBi this will be your password.
2. Make note of the server (the url of the Shooju instance you log in to), username (it's on the top of the My Profile page)
3. The first time you Invoke the functions in the section below you will be asked for credentials, like so:


After clicking Edit Credentials, select Basic, and put in your username and API Key from the steps above.

4. To change credentials either because of an error or because of API Key expiry, open Data source settings under File->Options and settings.

Read from Shooju

1. Under Get data, select Blank Query:

2. In the function editor, paste one of the below queries written in PowerBI’s M language:

• To get only fields:

= (query as text, fields as text, max_series as text) => let    Source = Json.Document(Web.Contents("https://trial.shooju.com/api/1/series",[Query = [query=query, fields=fields, per_page=max_series]]))[series],  Out = Table.ExpandRecordColumn(Table.ExpandRecordColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"fields"}, {"fields"}), "fields", Text.Split(fields, ","), Text.Split(fields, ","))in    Out

• To get both fields and points:

= (query as text, fields as text, max_points as text, date_from as text, date_to as text, max_series as text) => let    Source = Json.Document(Web.Contents("https://trial.shooju.com/api/1/series",[Query = [query=query, max_points=max_points, date_format="iso", fields=fields, df=date_from, dt=date_to, per_page=max_series]]))[series],    Custom1 = Table.TransformColumnTypes(Table.SplitColumn(Table.TransformColumns(Table.ExpandListColumn(Table.RemoveColumns(Table.ExpandRecordColumn(Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), "Column1", {"fields", "points", "series_id"}, {"Column1.fields", "Column1.points", "Column1.series_id"}),{"Column1.series_id"}), "Column1.points"), {"Column1.points", each Text.Combine(List.Transform(_, Text.From), ","), type text}), "Column1.points", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"dt", "val"}),{{"dt", type datetime}, {"val", type number}}),    Out = Table.ExpandRecordColumn(Custom1, "Column1.fields", Text.Split(fields, ","), Text.Split(fields, ","))in    Out

After pasting, change the URL from https://trial.shooju.com to the URL you use to access Shooju. Hit enter after pasting and making the change. You can name the function afterwards by right-clicking and hitting Rename:

You can now fill in the parameters and hit Invoke or use the function(s) wherever needed programatically. Below you’ll find the params that are used in above functions to get data from shooju.

• query
• SjQL query, same format used in Series Explorer. More info at Queries
• fields
• comma-separated (no spaces) list of fields you’d like to retrieve
• E.g. description would return only description field while description,sid,meta.processor would return description, sid and meta.processor fields
• If some of the fields you required are not available for some series, it will be displayed as null
• If you add any number of spaces to separate the fields,for instance description, sid,meta.processor the query will not work and it will return an error
• The * operator is not supported
• max_points
• Max number of points that should be returned for each series
• Use -1 to get all the points
• date_from
• Specifies the start time frame for points. Will only retrieve points for dates greater or equals to the specified date
• Dates can be set in any of the formats provided in Date Input Notation
• date_to
• Specifies the end time frame for points. Will only points for dates minor or equals to the specified date
• Dates can be set in any of the formats provided in Date Input Notation
• max_series
• Max number of series to retrieve for that query
• Accepts integer values ranging from 1 to 9999