This was a bit tricky. Each row object has an array of fields that needs to be transposed so each field becomes a column in a table.
So for each row, I essentially changed the format from this:
{
"fld": [
{
"i": 0,
"v": 5.320000171661377
},
{
"i": 1,
"v": "06-FEB-19 09:21:28.4"
},
{
"i": 2,
"v": "UDI_UT_3_601_001"
}
]
}to this:
{
"fld": [
{
"v1": 5.320000171661377,
"v2": "06-FEB-19 09:21:28.4",
"v3": "UDI_UT_3_601_001"
}
]
}Here's the Power Query M equivalent I came up with. To work for your purposes, replace the jsonText and jsonBinary steps below with your JSON source:
let
jsonText = "{""data"":[{""r"":""D"",""cols"":[{""i"":0,""n"":""ip_trend_value""},{""i"":1,""n"":""ip_trend_time""},{""i"":2,""n"":""name""}],""rows"":[{""fld"":[{""i"":0,""v"":5.320000171661377},{""i"":1,""v"":""06-FEB-19 09:21:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.309999942779541},{""i"":1,""v"":""06-FEB-19 09:22:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.309999942779541},{""i"":1,""v"":""06-FEB-19 09:23:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.300000190734863},{""i"":1,""v"":""06-FEB-19 09:24:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.320000171661377},{""i"":1,""v"":""06-FEB-19 09:25:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.320000171661377},{""i"":1,""v"":""06-FEB-19 09:26:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.320000171661377},{""i"":1,""v"":""06-FEB-19 09:27:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.320000171661377},{""i"":1,""v"":""06-FEB-19 09:28:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.300000190734863},{""i"":1,""v"":""06-FEB-19 09:29:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]},{""fld"":[{""i"":0,""v"":5.309999942779541},{""i"":1,""v"":""06-FEB-19 09:30:28.4""},{""i"":2,""v"":""UDI_UT_3_601_001""}]}]}]}",
jsonBinary = Text.ToBinary(jsonText),
jsonDocument = Json.Document(jsonBinary),
data = jsonDocument[data],
childNode = data{0},
cols = childNode[cols],
ConvertedColsToTable = Table.FromList(cols, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColNames = Table.ExpandRecordColumn(ConvertedColsToTable, "Column1", {"n"}, {"n"}),
colNames = ExpandedColNames[n],
rows = childNode[rows],
ConvertedListToTable = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedListOfFields = Table.ExpandRecordColumn(ConvertedListToTable, "Column1", {"fld"}, {"fld"}),
rowCount = Table.RowCount(ExpandedListOfFields),
transposeRecords = (n as number) =>
let
fld = ExpandedListOfFields{n}[fld],
ConvertedFldToTale = Table.FromList(fld, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
TransposedFldTable = Table.Transpose(ConvertedFldToTale),
ExpandedTrend = Table.ExpandRecordColumn(TransposedFldTable, "Column1", {"v"}, {"v"}),
ExpandedTime = Table.ExpandRecordColumn(ExpandedTrend, "Column2", {"v"}, {"v.1"}),
ExpandedName = Table.ExpandRecordColumn(ExpandedTime, "Column3", {"v"}, {"v.2"}),
ConvertedToRecord = Table.ToRecords(ExpandedName)
in
ConvertedToRecord,
listOfFields =
List.Generate(
()=>
[
n = 0,
records = transposeRecords(n)
],
each [n] < rowCount,
each [
n = [n] + 1,
records = transposeRecords(n)
]
),
ConvertedFldListToTable = Table.FromList(listOfFields, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedList = Table.ExpandRecordColumn(ConvertedFldListToTable, "Column1", {"records"}, {"records"}),
ExpandedRecords = Table.ExpandListColumn(ExpandedList, "records"),
ExpandedFldNameValues = Table.ExpandRecordColumn(ExpandedRecords, "records", {"v", "v.1", "v.2"}, colNames)
in
ExpandedFldNameValuesNote: this looks like the results from an Excel file on SharePoint using the Excel REST service. That service also produces an html of your Excel table. To get the html version, append $format=html to the end of the Uri.
