Go here to get Microsoft PowerBI desktop : https://powerbi.microsoft.com/fr-fr/desktop/?WT.mc_id=Blog_Desktop_Update

Step 1: create PowerBI Query

In Home, Get data menu select Web:

In the URL field put your Pilot Things URL composed of:

• The base URL which is your cloud or local URL, for instance https://dmsrc.pilot-things.com/. This URL is located in your browser when connected to your Pilot Things account,
• the suffix /api/measures.json?
• Parameters: detailed=true is mandatory

The copy screen hereunder represents an example of composed URL:

Once you have clicked on Ok, fill your Pilot Things account details (user and password) selecting Base authentication:

Step 2: create an Excel query

In the query Editor, select advanced Editor:

 Replace the selected text here under by:

,data = Source[data],
#"Convert table"=Table.FromList(data,Splitter.SplitByNothing(),null,null,ExtraValues.Error),
    #"Expand" = Table.ExpandRecordColumn(#"Convert table", "Column1", {"timestamp", "thing", "attribute", "value"}, {"DATET", "Thing","Measurement","Value"}),
    #"GET Type" = Table.ExpandRecordColumn(#"Expand", "Measurement", {"name"}, {"Measurement"}),
    #"GET Thing" = Table.ExpandRecordColumn(#"GET Type", "Thing", {"name"}, {"Thing"}),
    #"Format Time" = Table.AddColumn(#"GET Thing", "Division", each [DATET] / 1000, type number),
    #"Format Time 2" = Table.TransformColumns(#"Format Time", {{"Division", each _ + -18000, type number}}),
    #"Format Time 3" = Table.AddColumn(#"Format Time 2", "Division.1", each [Division] / 86400, type number),
    #"Format Time 4" = Table.AddColumn(#"Format Time 3", "Date", each [Division.1] + 25569, type number),
    #"Convert Time" = Table.TransformColumnTypes(#"Format Time 4",{{"Date", type datetime}}),
    #"Remove" = Table.RemoveColumns(#"Convert Time",{"DATET", "Division", "Division.1"}),
    #"ORDER" = Table.ReorderColumns(#"Remove",{"Date","Thing","Measurement","Value"}),
    #"DATA" = Table.Sort(#"ORDER",{{"Date", Order.Descending}})
in
    #"DATA"

In order to get that:

Click Done and then Close & Load

You now ready to use the data into PowerBI