This article explains how to share your IoT network data to Microsoft Excel.

You need Microsoft Excel 2013 or later installed on your windows computer. Mac excel does not support data web connections.

Step 1 : create a web query in Excel

Open Excel and create a new spreadsheet. In the data tab, select From 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://kerlink.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
  • limit=xxx is optional, xxx is the number of measurements. By default the limit is 100
  • sort=timestamp&dir=DESC to get the last measurements

Parameters are separated by a &.

The hereunder example returns the last 1000 measurements:

https://kerlink.pilot-things.com/api/measures.json?detailed=true&limit=1000&sort=timestamp&dir=DESC

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

Click on Connect to finish the connection setup.

Step 2: create an Excel query

In the query Editor, select advanced Editor:

 Replace the selected text here under by:

  data = Source[data],
 #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "dtype", "timestamp", "attribute", "value", "thing", "product", "model"}, {"Column1.id", "Column1.dtype", "Column1.timestamp", "Column1.attribute", "Column1.value", "Column1.thing", "Column1.product", "Column1.model"}),
 #"Expanded Column1.attribute" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.attribute", {"id", "name", "unit", "type"}, {"Column1.attribute.id", "Column1.attribute.name", "Column1.attribute.unit", "Column1.attribute.type"}),
 #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1.attribute",{"Column1.id", "Column1.dtype", "Column1.attribute.id", "Column1.product", "Column1.model"}),
 #"Expanded Column1.attribute.type" = Table.ExpandRecordColumn(#"Removed Columns", "Column1.attribute.type", {"name", "type"}, {"Column1.attribute.type.name", "Column1.attribute.type.type"}),
 #"Expanded Column1.thing" = Table.ExpandRecordColumn(#"Expanded Column1.attribute.type", "Column1.thing", {"displayName"}, {"Column1.thing.displayName"}),
 #"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1.thing",{"Column1.thing.displayName", "Column1.timestamp", "Column1.attribute.name", "Column1.attribute.unit", "Column1.value", "Column1.attribute.type.name", "Column1.attribute.type.type"}),
 #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column1.attribute.type.name", "attribute.type.name"}, {"Column1.attribute.type.type", "attribute.type"}}),
 #"Added Custom" = Table.AddColumn(#"Renamed Columns", "timestamp", each #datetime(1970,1,1,0,0,0)+#duration(0,0,0,[Column1.timestamp]/1000)),
 #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"timestamp", "Column1.thing.displayName", "Column1.timestamp", "Column1.attribute.name", "Column1.attribute.unit", "Column1.value", "attribute.type.name", "attribute.type"}),
 #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Column1.timestamp"}),
 #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns1",{"timestamp", "Column1.thing.displayName", "Column1.attribute.name", "Column1.value", "Column1.attribute.unit", "attribute.type.name", "attribute.type"}),
 #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns2",{{"Column1.attribute.name", "attribute"}, {"Column1.thing.displayName", "things.name"}, {"Column1.value", "value"}, {"Column1.attribute.unit", "unit"}})
in
 #"Renamed Columns1"

In order to get that:

Click Done and then Close & Load

You will get mesurements from your Pilot Things smart IoT network in an excel table named measures:

If you wish Excel to refresh the data automatically, select Connection Properties in the Table Tools Design tab: