Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am facing an issue in later steps of my data modeling in PowerQuery. After some transformations I am using the Json parse to extract some information from the Json messages.
Most of the Json parsing is ok up until I reach a point where one of the Json elements is a list and that list is made up of records, this is a problem for PowerQuery since it wont allow me to extract those values due to:
This list can contain many records ( although most likely wont get any bigger than 4 or 5) , and each record is a Json object with some elements (of which I only need 2 for now). My biggest issue is getting through the point were i cant extract those records as extra columns like in the rest of the parsing process ( using the option to expand on new lines will cause me to have duplicates and headaches since this table has many other columns and will use relations later).
There is a similar issue already reported:
But I cant get my head around the solution suggested there by @MarcelBeug as it is tailored to that specific case.
Can anyone help me understand what the issue really is and how to deal with it?
Many thanks in advance!
Hi @v-rongtiep-msft !
I think I understand the code you post but I dont think that works. I believe that is the code for expanding a records column ( that is what is automatically generated when hitting the expand icon on the top right of any records column). That works for the previous steps indeed.
The issue is my column is a list column and when trying to extract those values in the lists to new columns it gives the errors since it can only do that operation with text and apparently records cant be converted to text.
Also I dont know how many records are there in the lists some might have one, some 2 or could be more, theres no way to know it beforehand.
I think I actually solved this part by extracting the values onto new rows ( which created duplicates ) and then pivoting the columns ( which I think, not sure tho, returned the table to no duplicates and added the values on the extra rows back to columns).
Thanks for help anyways!
Hi @AutoJL ,
The error message you are seeing is due to the fact that you are trying to convert a record to text. In Power Query, you can use the Table.ExpandRecordColumn function to expand the records in a list into separate columns. Here is an example of how to use this function:
let
Source = Json.Document(JsonText),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"Column1", "Column2"}, {"Column1", "Column2"})
in
#"Expanded Value"
I have also found a similar post, please refer to it to see if it helps you.
apply - POWER QUERY [Expression.Error] Cannot convert the value null to type Table - Stack Overflow
How to Get Your Question Answered Quickly
If it does not help, please provide more details.
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |