# 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

```
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

```
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
> 
> ```
> let
> ...





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
> 
> ```
> let
> ...




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.


```
{
    "@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



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


----------



## 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


----------



## anvg (Nov 28, 2015)

Please, try

```
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
```
But I did not see a like structure of data between records and lists
Regards,


----------



## sankallada (Nov 29, 2015)

Hi anvg,

Thanks a lot for your help and consideration and really sorry for the delayed reply.


----------

