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

sankallada · Nov 26, 2015

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

ImkeF · Nov 26, 2015

If they contain the correct information but just in different formats (you can check this by clicking on any of them: This will create additional step(s) that you can later delete), you could open them differently by using an if-Statement.

If the data doesn't come out right, you should send a frown to the PQ team - they are really helpful.

sankallada · Nov 26, 2015

ImkeF said:
If they contain the correct information but just in different formats (you can check this by clicking on any of them: This will create additional step(s) that you can later delete), you could open them differently by using an if-Statement.

If the data doesn't come out right, you should send a frown to the PQ team - they are really helpful.

Hi,

Thanks for the quick response.
Currently this is what I can see in the advanced editor.

let
Source = Json.Document(Web.Contents("http://www.acmee.com/feeds/listing.json")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{1}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"propCode", "name", "hours"}, {"Column1.propCode", "Column1.name", "Column1.hours"}),
#"Expanded Column1.hours" = Table.ExpandListColumn(#"Expanded Column1", "Column1.hours")
in
#"Expanded Column1.hours"

What I'm looking is to list the values in the same excel only [ie: the record has two fields day and time holding text values and the list has a string value but in some cases this may be empty].

Can yo please help me to achieve this?

Thanks

anvg · Nov 26, 2015

Hi
If you have a list data order equal a record data order and equal item count in them then you could do like this

Code:

let
    myTable = #table(type table [field1 = number,field2=text,field3=any],
              {{ 1, "text1", 3},
               { 2, "text2", null },
               { 3, "text3", {1, "ListName1", 201.11} },
               { 4, "text4", [code=2,name="RecName1",sum=101.11] },
               { 5, "text5", {3, "ListName2", 212.12} },
               { 6, "text6", [code=4, name="RecName2", sum=123.46] }
               }
              ),
    ListToRecord = Table.TransformColumns(myTable, { {"field3", each 
                                          if Value.Is(_, type list) then Record.FromList(_, {"code", "name", "sum"})
                                          else _} }),
    SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([field3], type record)),
    expanded = Table.ExpandRecordColumn(SelRecOnly, "field3", {"code", "name", "sum"}, {"code", "name", "sum"})
in
    expanded

Regards,

anvg · Nov 26, 2015

I tried and got than an equal items order between record fields and a list items is not required. It is needed an equal items order from a list to a list, for example

Code:

let
    myTable = #table(type table [field1 = number,field2=text,field3=any],
              {{ 1, "text1", 3},
               { 2, "text2", null },
               { 3, "text3", {"ListName1", 1, 201.11} },
               { 4, "text4", [code=2,name="RecName1",sum=101.11] },
               { 5, "text5", {"ListName2", 3, 212.12} },
               { 6, "text6", [code=4, name="RecName2", sum=123.46] }
               }
              ),
    ListToRecord = Table.TransformColumns(myTable, { {"field3", each 
                                          if Value.Is(_, type list) then Record.FromList(_, {"name", "code", "sum"})
                                          else _} }),
    SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([field3], type record)),
    expanded = Table.ExpandRecordColumn(SelRecOnly, "field3", {"code", "name", "sum"}, {"code", "name", "sum"})
in
    expanded

sankallada · Nov 27, 2015

anvg said:

I tried and got than an equal items order between record fields and a list items is not required. It is needed an equal items order from a list to a list, for example

Code:

let
    myTable = #table(type table [field1 = number,field2=text,field3=any],
              {{ 1, "text1", 3},
               { 2, "text2", null },
               { 3, "text3", {"ListName1", 1, 201.11} },
               { 4, "text4", [code=2,name="RecName1",sum=101.11] },
               { 5, "text5", {"ListName2", 3, 212.12} },
               { 6, "text6", [code=4, name="RecName2", sum=123.46] }
               }
              ),
    ListToRecord = Table.TransformColumns(myTable, { {"field3", each 
                                          if Value.Is(_, type list) then Record.FromList(_, {"name", "code", "sum"})
                                          else _} }),
    SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([field3], type record)),
    expanded = Table.ExpandRecordColumn(SelRecOnly, "field3", {"code", "name", "sum"}, {"code", "name", "sum"})
in
    expanded

Thanks a lot,
I'm able to modify the M script by following the example that you have given as follows.

let
Source = Json.Document(Web.Contents("http://www.acmee.com/feeds/listing.json")),
thingstodo = Source[thingstodo],
#"Converted to Table" = Table.FromList(thingstodo, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
myTable = #"Converted to Table",
#"Expanded Column2" = Table.ExpandRecordColumn(myTable, "Column1", {"propCode", "name", "type", "market", "learnMoreURL", "mainImageUrl", "logoImageUrl", "hours", "url"}, {"Column1.propCode", "Column1.name", "Column1.type", "Column1.market", "Column1.learnMoreURL", "Column1.mainImageUrl", "Column1.logoImageUrl", "Column1.hours", "Column1.url"}),
ListToRecord = Table.TransformColumns(#"Expanded Column2", { {"Column1.hours", each
if Value.Is(_, type list) then Record.FromList(_, {"day", "time"})
else _} }),
SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([Column1.hours], type record)),
#"Expanded Column1.hours" = Table.ExpandRecordColumn(SelRecOnly, "Column1.hours", {"day", "time"}, {"Column1.hours.day", "Column1.hours.time"}),
#"Expanded Column1.hours.day1" = Table.ExpandRecordColumn(#"Expanded Column1.hours", "Column1.hours.day", {"day", "time"}, {"Column1.hours.day.day", "Column1.hours.day.time"}),
#"Expanded Column1.hours.time1" = Table.ExpandRecordColumn(#"Expanded Column1.hours.day1", "Column1.hours.time", {"day", "time"}, {"Column1.hours.time.day", "Column1.hours.time.time"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.hours.time1",{{"Column1.propCode", "Property Code"}, {"Column1.name", "Property Name"}, {"Column1.type", "Resource Type"}, {"Column1.market", "Market Code"}, {"Column1.learnMoreURL", "LearnMoreURL"}, {"Column1.mainImageUrl", "MainImageUrl"}, {"Column1.logoImageUrl", "LogoImageUrl"}, {"Column1.hours.day.day", "Day Range 1"}, {"Column1.hours.day.time", "Time Range 1"}, {"Column1.hours.time.day", "Day Range 2"}, {"Column1.hours.time.time", "Time Range 2"}, {"Column1.url", "URL"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Property Code", Order.Ascending}})
in
#"Sorted Rows"

But this ends up in an error which says that :

"Expression.Error: 2 keys were specified, but 1 values were provided.
Details:
List"

But If I avoid the step,

SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([Column1.hours], type record)), #"Expanded Column1.hours" = Table.ExpandRecordColumn(SelRecOnly, "Column1.hours", {"day", "time"}, {"Column1.hours.day", "Column1.hours.time"}),

It will give me data from records (as expected) but the columns with list will be converted to Error.
As I stated earlier, the record and list holds different number of items. A record has always two fields day and time. Almost all cases the the list will hold a single value.
I assume that this is causing the error.
In any way we can bypass this error?

Regards

sankallada · Nov 27, 2015

anvg said:

I tried and got than an equal items order between record fields and a list items is not required. It is needed an equal items order from a list to a list, for example

Code:

let
    myTable = #table(type table [field1 = number,field2=text,field3=any],
              {{ 1, "text1", 3},
               { 2, "text2", null },
               { 3, "text3", {"ListName1", 1, 201.11} },
               { 4, "text4", [code=2,name="RecName1",sum=101.11] },
               { 5, "text5", {"ListName2", 3, 212.12} },
               { 6, "text6", [code=4, name="RecName2", sum=123.46] }
               }
              ),
    ListToRecord = Table.TransformColumns(myTable, { {"field3", each 
                                          if Value.Is(_, type list) then Record.FromList(_, {"name", "code", "sum"})
                                          else _} }),
    SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([field3], type record)),
    expanded = Table.ExpandRecordColumn(SelRecOnly, "field3", {"code", "name", "sum"}, {"code", "name", "sum"})
in
    expanded

Thanks a lot,
I'm able to modify the M script by following the example that you have given as follows.

let
Source = Json.Document(Web.Contents("http://www.acmee.com/feeds/listing.json")),
thingstodo = Source[thingstodo],
#"Converted to Table" = Table.FromList(thingstodo, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
myTable = #"Converted to Table",
#"Expanded Column2" = Table.ExpandRecordColumn(myTable, "Column1", {"propCode", "name", "type", "market", "learnMoreURL", "mainImageUrl", "logoImageUrl", "hours", "url"}, {"Column1.propCode", "Column1.name", "Column1.type", "Column1.market", "Column1.learnMoreURL", "Column1.mainImageUrl", "Column1.logoImageUrl", "Column1.hours", "Column1.url"}),
ListToRecord = Table.TransformColumns(#"Expanded Column2", { {"Column1.hours", each
if Value.Is(_, type list) then Record.FromList(_, {"day", "time"})
else _} }),
SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([Column1.hours], type record)),
#"Expanded Column1.hours" = Table.ExpandRecordColumn(SelRecOnly, "Column1.hours", {"day", "time"}, {"Column1.hours.day", "Column1.hours.time"}),
#"Expanded Column1.hours.day1" = Table.ExpandRecordColumn(#"Expanded Column1.hours", "Column1.hours.day", {"day", "time"}, {"Column1.hours.day.day", "Column1.hours.day.time"}),
#"Expanded Column1.hours.time1" = Table.ExpandRecordColumn(#"Expanded Column1.hours.day1", "Column1.hours.time", {"day", "time"}, {"Column1.hours.time.day", "Column1.hours.time.time"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.hours.time1",{{"Column1.propCode", "Property Code"}, {"Column1.name", "Property Name"}, {"Column1.type", "Resource Type"}, {"Column1.market", "Market Code"}, {"Column1.learnMoreURL", "LearnMoreURL"}, {"Column1.mainImageUrl", "MainImageUrl"}, {"Column1.logoImageUrl", "LogoImageUrl"}, {"Column1.hours.day.day", "Day Range 1"}, {"Column1.hours.day.time", "Time Range 1"}, {"Column1.hours.time.day", "Day Range 2"}, {"Column1.hours.time.time", "Time Range 2"}, {"Column1.url", "URL"}}),
#"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Property Code", Order.Ascending}})
in
#"Sorted Rows"

But this ends up in an error which says that :

"Expression.Error: 2 keys were specified, but 1 values were provided.
Details:
List"

But If I avoid the step,

SelRecOnly = Table.SelectRows(ListToRecord, each Value.Is([Column1.hours], type record)), #"Expanded Column1.hours" = Table.ExpandRecordColumn(SelRecOnly, "Column1.hours", {"day", "time"}, {"Column1.hours.day", "Column1.hours.time"}),

It will give me data from records (as expected) but the columns with list will be converted to Error.
As I stated earlier, the record and list holds different number of items. A record has always two fields day and time. Almost all cases the the list will hold a single value.
I assume that this is causing the error.
In any way we can bypass this error?

Note:
------------------------------------------------------------------------------------------------------------------------------------------------------
Just now I noticed that if the field - hours- in the json file is having the following structure:

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

the M script will work fine.
If, the number of objects in side JSON is less than or greater than 2, the script is failing to exact information

sankallada · Nov 27, 2015

Hi ,

I'm adding a sample input file to know the structure.

Code:

{
    "@timestamp": "27-11-2015   16:39:03 PM",
    "thingstodo": [{
        "name": "NoName",
        "hours": [{
            "day": "Monday-Thursday",
            "time": "2:00PM - 1:00AM"
        },
        {
            "day": "Friday-Sunday",
            "time": "10:00AM - 2:00AM"
        }]
    },
    {
        "name": foo1",
        "brand": "bar1",
        "hours": [{
            "day": "Friday and Saturday",
            "time": "5:00PM - 11:00PM"
        }]
    },
    {
        "name": "foo2",
        "brand": "bar2",
        "hours": [{
            "day": "Friday-Saturday",
            "time": "4:00PM - 4:00AM"
        },
        {
            "day": "Sunday",
            "time": "4:00PM - 1:00AM"
        },
        {
            "day": " Monday-Thursday",
            "time": "4:00PM - 12 NOON next day"
        },
        []]
    },
    {
        "name": "foo3",
        "brand": "bar3",
        "hours": [{
            "day": "Sun-Thurs",
            "time": "9:00AM - 8:00PM"
        },
        {
            "day": "Fri-Sat",
            "time": "8:00AM - 8:00PM "
        }]
    },
    {
        "name": "foo4",
        "brand": "bar4",
    },
    {
        "name": "foo5",
        "brand": "bar5",
        "hours": [{
            "day": "Daily",
            "time": "24 Hours"
        }]
    },
    {
        "name": "foo6",
    },
    {
        "name": "Harrah's Resort Atlantic City’s Waterfront Shops",
    },
    {
        "name": "foo7",
        "brand": "bar7",
    },
  [COLOR=#ff0000]  {
        "name": "foo8",
        "hours": [{
            "day": "Sunday",
            "time": "12:00PM - 11:00PM"
        },
        {
            "day": "Monday, Wednesday, Thursday ",
            "time": "5:00PM - 11:00PM"
        },
        {
            "day": "Tuesday",
            "time": "Closed"
        },
        {
            "day": "Friday ",
            "time": "5:00PM - 1:00AM"
        },
        {
            "day": "Saturday ",
            "time": "12:00PM - 1:00AM"
        }]
    },[/COLOR]
    {
        "name": "foo9",
        "brand": "bar9",
        "hours": [{
            "day": "Wed & Fri",
            "time": "10:00PM - 4:00AM"
        },
        {
            "day": "Sat",
            "time": "10:00PM - 4:00AM"
        }]
        }
    },
    {
        "name": "foo10",
        "hours": [{
            "day": "Sunday",
            "time": "8:00AM - 6:00PM"
        },
        {
            "day": "Monday",
            "time": "8:00AM - 12:00AM"
        },
        {
            "day": "Tuesday",
            "time": "8:00AM - 12:00AM"
        },
        {
            "day": "Wednesday",
            "time": "8:00AM - 12:00AM"
        },
        {
            "day": "Thursday",
            "time": "8:00AM - 12:00AM"
        },
        {
            "day": "Friday",
            "time": "8:00AM - 12:00AM"
        },
        {
            "day": "Saturday",
            "time": "8:00AM - 12:00AM"
        }]
    },
    {
        "name": "foo11",
        "hours": [{
            "day": "Sun-Thu",
            "time": "11:00AM - 9:00PM"
        },
        {
            "day": "Fri & Sat",
            "time": "10:00AM - 10:00PM"
        }],
    }
    }]
}

As I pointed out earlier, if the hours have 2 records, the M script will work fine otherwise (less than or greater than 2) is failing.
Sample error
----------------------------------
Expression.Error: 2 keys were specified, but 7 values were provided.
Details:
List

regards,

anvg · Nov 27, 2015

Hi
I am sorry. Your json text example contains many mistakes. I tried to validate it on codebeautify.org/jsonvalidate, but I know json badly. I did not able to correct it and load to Power Query.
Regards,

sankallada · Nov 28, 2015

anvg said:
Hi
I am sorry. Your json text example contains many mistakes. I tried to validate it on codebeautify.org/jsonvalidate, but I know json badly. I did not able to correct it and load to Power Query.
Regards,

hi,

I'm really sorry for the error.
My original json file has around 20000 rows.
I'm attaching the new version, with corrections.

Lot of thanks for your time and consideration

Code:

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

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

sankallada

New Member

Excel Facts

ImkeF

Well-known Member

sankallada

New Member

anvg

Active Member

anvg

Active Member

sankallada

New Member

sankallada

New Member

sankallada

New Member

anvg

Active Member

sankallada

New Member

Similar threads

Forum statistics

Share this page

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

New Member

Excel Facts

Well-known Member

New Member

Active Member

Active Member

New Member

New Member

New Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock