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
Comments
0 comments
Please sign in to leave a comment.