CODEWITHSUNDEEP
jsontransformationpowerquery
kallada
kallada

Reputation: 1929

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

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.

screenshot for power query

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.

Upvotes: 4

Views: 15532

Answers (1)

kallada
kallada

Reputation: 1929

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

Upvotes: 3

Related Questions