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

sankallada

New Member
Joined
Nov 26, 2015
Messages
8
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.

fUJv7.png



Any pointers to solve this issue would be very helpful
Thanks in advance
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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.
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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


 
Upvote 0
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,

 
Upvote 0
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,
 
Upvote 0
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"
        }
      ]
    }
	
	]
}
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top