cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GCQUK Frequent Visitor
Frequent Visitor

Read Json

Hello,

I need to read a json file in the format below, but the json that the API returns is not in very common formatting. How do I transform this json into a table?



EX: 
___________________________________
_trend_value || ip_trend_time || name 
0 || dd/mm/yyyy || xxxxxx 
0 || dd/mm/yyyy || xxxxxx 
___________________________________

 

 

Json:
{
"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"
}
]
}
]
}
]
}

1 ACCEPTED SOLUTION

Accepted Solutions
tonmcg Regular Visitor
Regular Visitor

Re: Read Json

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
    ExpandedFldNameValues

Note: 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.

2 REPLIES 2
tonmcg Regular Visitor
Regular Visitor

Re: Read Json

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
    ExpandedFldNameValues

Note: 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.

GCQUK Frequent Visitor
Frequent Visitor

Re: Read Json

Thanks for the answer
It worked very well. It really is a REST service, but I do not know the technology used, but your solution solved the problem!