How to pivot column keeping null?

[Anonymous]

Hi,

I'm working with table created by Json data after transforming the data one of the column contains records and whitin the records it contains List of key value pairs. However in some rows there are no "Record" values.

>After I click on that expand at top right i get this>>

Now within the list there are multiple records and each record has multiple Key value pairs of data.

So in PowerBI I first expanded the List which gave me multiple rows of records and then I expanded the record to just two key value pairs. After I do that now when I try to Pivot the data of those two columns I get the error 

"Expression.Error: We cannot apply operator < to types Record and Record.
Details:
Operator=<
Left=[Record]
Right=[Record]"

I'm assuming thats because there are null values in some columns at the begineeing and thus the left is not matching the right can someone advise how to fix this?

[v-alq-msft]

Hi, @Anonymous 

I wonder what the data structures of the lists are like. It is hard to understand the problem without some sample data. Do mask sensitive data before uploading. Thanks.

Best Regards

Allan

[Anonymous]

Whats happening I post a reply and when I refresh the page the reply disappear 😞

Hi @Pragati11 @v-alq-msft,

Please find the sample pbix attached. link 

Please note the datatype of the data column is Json and there are few coumns where there is no data and when thats the case I would like to keep it null/empty.

Below is the table how I have right now 

id	name	data
1	a
2	b	{"CustomAttribute":[{"ExternalId":"123","Name":"Project Number","Data":"123456789"},{"ExternalId":"124","Name":"Purpose","Data":"ID"},{"ExternalId":"125","Name":"Approver","Data":"John Doe"},{"ExternalId":"126","Name":"Internal/external","Data":"Internal"},{"ExternalId":"127","Name":"person Name"}]}
3	c	{"CustomAttribute":[{"ExternalId":"123","Name":"Project Number","Data":"123456790"},{"ExternalId":"124","Name":"Purpose","Data":"BD"},{"ExternalId":"125","Name":"Approver","Data":"John Doe"},{"ExternalId":"126","Name":"Internal/external","Data":"Internal"},{"ExternalId":"127","Name":"person Name","Data": "Henry Doe"}]}
4	d	{"CustomAttribute":[{"ExternalId":"123","Name":"Project Number","Data":"123456791"},{"ExternalId":"124","Name":"Purpose","Data":"CD"},{"ExternalId":"125","Name":"Approver","Data":"John Doe"},{"ExternalId":"126","Name":"Internal/external","Data":"Internal"},{"ExternalId":"127","Name":"person Name","Data": "Patrick Doe"}]}

And this is how I want the above table to be converted to

id	name	Project Number	Purpose	Approver	Internal/external	person Name
1	a
2	b	123456789	ID	John Doe	Internal
3	c	123456790	BD	John Doe	External	Henry Doe
4	d	123456791	CD	John Doe	Internal	Patrick Doe

[Pragati11]

Hi @Anonymous ,

Can you try replacing null with some value like "No Value" (if other values are text) or 0 (if other values are numeric)?

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 ,

I tried to pivot with null and got the below error

Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]

but when I replace the null with anything else and try again i get the below error

Expression.Error: We cannot apply operator < to types Record and Record.
Details:
Operator=<
Left=[Record]
Right=[Record]

[Pragati11]

Hi @Anonymous ,

Just wanted to mention here as it is hard to comment further without seeing any sample data or your pbix file. It will be helpful.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati