Forum Discussion
How to pivot column keeping null?
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?
5 Replies
- Pragati11
Super User
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
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
v-alq-msftCommunity Support
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
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. linkPlease 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
