Transforming json with power query (mix of list and record in a single column)

Question

I'm trying to transform a (I'm a newbie in power query transformation) json data. Our aim is to merge and transform a few json files to create a report. The json files are provided by different feed exposed by our web application. As part of learning, we tried to transform a single json file.

But the one of the field in our json data is is recognized by power query as a mix of record and list types (one of the column in power query is showing a mix of list and record).

In this case, I'm unable to grab the values of these line items.

Any pointers to solve this issue would be very helpful

Thanks in advance

Please find the sample json :

{
   "@timestamp": "27-11-2015   21:31:30 PM",    "thingstodo": [{
    "propCode": "foo1"
},
{
    "propCode": "foo2"
},
{
    "propCode": "foo3",
    "hours": [{
        "day": "Monday-Thursday",
        "time": "2:00PM - 1:00AM"
    },
    {
        "day": "Friday-Sunday",
        "time": "10:00AM - 2:00AM"
    }]
},
{

    "propCode": "foo4"
},
{


    "propCode": "foo5",
    "hours": [{
        "day": "Daily",
        "time": "24 Hours"
    }]
},
{


    "propCode": "foo6",
    "hours": [{
        "day": "10am - 4am",
        "time": "bar & lounge area"
    },
    {
        "day": "12pm - 4am",
        "time": "gaming area"
    }]
},
{
    "propCode": "foo7",


    "hours": [{
        "day": "Daily",
        "time": "11:00AM - 1:00AM"
    },
    {
        "day": "Happy Hour Daily",
        "time": "4:00PM - 6:00PM"
    }]
},
{
    "propCode": "foo8"
},
{
    "propCode": "foo9"


},
{
    "propCode": "foo10",
    "hours": [{
        "day": "Sun-Thu",
        "time": "10:00AM - 11:00PM"
    },
    {
        "day": "Fri & Sat",
        "time": "10:00AM - 12:00AM"
    }]
},
{
    "propCode": "foo11",
    "name": "Atlantic City Deals and Packages",
    "brand": "harrahs"
},
{
    "propCode": "foo12"
},
{


    "propCode": "foo13",
    "hours": [{
        "day": "Sun-Thu",
        "time": "11:00AM - 1:00AM"
    },
    {
        "day": "Fri-Sat",
        "time": "11:00AM - 2:00AM"
    }]
},
{
    "propCode": "foo8",


    "hours": [{
        "day": "Daily",
        "time": "11:00AM - 2:00AM"
    }]
},
{
    "propCode": "foo12"


},
{
    "propCode": "foo14"
},
{


    "propCode": "foo14",
    "hours": [{
        "day": "Daily",
        "time": "6:00AM - 6:00PM"
    }]
},
{


    "propCode": "foo12",
    "hours": [{
        "day": "Sunday-Thursday",
        "time": "8:00AM - 6:00PM"
    },
    {
        "day": "Friday-Saturday",
        "time": "8:00AM - 7:00PM<br>"
    },
    {
        "day": "<br>Adult Swim (21+)<br> Sunday-Thursday",
        "time": "5:00PM - 6:00PM"
    },
    {
        "day": "Friday-Saturday",
        "time": "5:00PM - 7:00PM"
    }]
},
{
    "propCode": "foo15",


    "hours": [{
        "day": "Mon-Thu ",
        "time": "9:00AM - 9:30PM "
    },
    {
        "day": "Fri-Sun ",
        "time": "9:00AM - Midnight"
    }]
},
{


    "propCode": "foo16",
    "hours": [{
        "day": "Sun - Thurs:",
        "time": "9:00AM - 5:00PM"
    },
    {
        "day": "Fri - Sat:",
        "time": "9:00AM - 6:00PM"
    },
    {
        "day": "Slide",
        "time": "Closed for the season"
    }]
},
{
    "propCode": "foo17",


    "hours": [{
        "day": "Friday",
        "time": "10:30PM "
    },
    {
        "day": "Saturday",
        "time": "10:30PM "
    }]
},
{


    "propCode": "foo6"
},
{
    "propCode": "foo14",

    "hours": [{
        "day": "Sunday - Friday",
        "time": "9:00AM - 5:00PM"
    },
    {
        "day": "Saturday",
        "time": "9:00AM - 6:00PM"
    }]
},
{


    "propCode": "foo14",
    "hours": [{
        "day": "Closed for ",
        "time": "Winter Season"
    }]
},
{


    "propCode": "foo12",
    "hours": [["CLOSED"]]
},
{
    "propCode": "foo18",


    "hours": [{
        "day": "Box Office Tue-Sat",
        "time": "12:00PM - 8:00PM"
    }]
},
{
    "propCode": "foo19"
},
{


    "propCode": "foo20",
    "hours": [{
        "day": "Monday - Thursday",
        "time": "<br>12:00PM - 3:00AM"
    },
    {
        "day": "Friday - Sunday",
        "time": "<br>10:00AM - 3:00AM"
    },
    {
        "day": "Live Music",
        "time": ", 6:00PM - 10:00PM"
    }]
},
{


    "propCode": "foo6",
    "hours": [{
        "day": "Sunday:",
        "time": "5:00PM - 1:00AM "
    },
    {
        "day": "Monday - Thursday:",
        "time": "5:00PM - 1:00AM "
    },
    {
        "day": "Friday:",
        "time": "5:00PM - 2:00AM "
    },
    {
        "day": "Saturday:",
        "time": "5:00PM - 1:00AM "
    }]
},
{


    "propCode": "foo16",
    "hours": [{
        "day": "Daily",
        "time": "24 Hours"
    }]
},
{
    "propCode": "foo21",


    "hours": [{
        "day": "Mon-Sat",
        "time": "10:00AM - 2:00AM"
    },
    {
        "day": "Sun",
        "time": "12:00PM - 2:00AM"
    }]
},
{
    "propCode": "foo10"
},
{
    "propCode": "foo23",
    "hours": [{
        "day": "Open Daily <br>(Seasonally)",
        "time": "11:00AM - 6:00PM"
    }]
},
{
    "propCode": "foo22",
    "hours": [{
        "day": "Daily",
        "time": "11:00AM - 3:00AM"
    }]
},

   {
  "propCode": "foo23",

  "hours":       [
            {
      "day": "Sun - Thurs:",
      "time": "9:00AM - 5:00PM"
    },
            {
      "day": "Fri - Sat:",
      "time": "9:00AM - 6:00PM"
    },
            {
      "day": "Slide",
      "time": "Closed for the season"
    }
  ]
}

]

}

Note One object in this file should represent a row in excel.

Does the list correspond to a particular field in the record? You could add a custom column that would convert the list into a record and keep records as they are. You could then unpack the records as you see fit. — Alejandro Lopez-Lago - MSFT
– Alejandro Lopez-Lago - MSFT, Commented Nov 26, 2015 at 1:09
If I click on the list it shows a string value, In some cases it is empty too. But for the record there are two fields in it "day" and "time" (both of them holds string values). The list is not corresponding to any particular field in record. — kallada
– kallada, Commented Nov 26, 2015 at 1:27
Could you include an example of the JSON input you are trying to parse, and the output table you want to transform it to? — Carl Walsh
– Carl Walsh, Commented Nov 29, 2015 at 18:56

Sergey Lossev · Accepted Answer · 2016-04-04 02:43:11Z

3

I wanted to share an external link here (not sure whether I'm violating any policies, please correct me if so) so that someone else can also use this knowledge in need. My core issue got resolved with help of www.mrexcel.com/forum/power-bi. With this snippet, I'm able to create an excel based tool to prepare a data set for business by combining different feeds. Please find the code required for this (M, but very specific to my input file)

let
    source = Json.Document(File.Contents("d:\path\filename.json")),
    tabled = Table.FromRecords({source}),
    expandListField = Table.ExpandListColumn(tabled, "thingstodo"),
    expandRecField  = Table.ExpandRecordColumn(expandListField, "thingstodo", {"propCode", "hours"}, {"propCode", "hours"}),
    expandList2  = Table.ExpandListColumn(expandRecField, "hours"),
    fieldForRec  = Table.AddColumn(expandList2,"Rec",each if Value.Is([hours], type record) then [hours] else null,type record),
    fieldForList = Table.AddColumn(fieldForRec, "List",each if Value.Is([hours], type list) then [hours] else null,type list),
    removed = Table.RemoveColumns(fieldForList, {"hours"}),
    expandRecField2 = Table.ExpandRecordColumn(removed, "Rec", {"day", "time"}, {"day", "time"}),
    expandList3 = Table.ExpandListColumn(expandRecField2, "List")
in
    expandList3

Please find a link to this forum below:

http://www.mrexcel.com/forum/power-bi/904936-transforming-json-power-query-mix-list-record-single-column-2.html

Thanks

edited Apr 4, 2016 at 2:43

Sergey Lossev

1,54011 silver badges21 bronze badges

answered Nov 30, 2015 at 23:12

kallada

1,9596 gold badges38 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Piotr Olaszewski Over a year ago

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

kallada Over a year ago

@Piotr Olaszewski I have edited my answer inline with your suggestion. Thanks.

Collectives™ on Stack Overflow

Transforming json with power query (mix of list and record in a single column)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related