Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI
I have a JSON which looks like this:
{ } JSON
{ } groups
[ ] columns
If I expand the groups object, it looks like this:
{ } JSON
{ } groups
{ } g1
* label : "Flower / Fruit"
{ } g2
* label : "On Tree"
{ } g3
* label : "Inner / Outer Leaves"
[ ] columns
I'm trying to get the GROUPS object in this format :
group | lable
g1 | Flower / Fruit
g2 | On Tree
g3 | Inner / Outer Leaves
Now I can extract the values, and then split to rows, but the problem I am facing is that in the same table and field, other JSON objects can have "group" objects with different or more values, and it looks like when you extract values from the JSON, you have to name them, thus I suspect I may have to do this with some M code ?
Does anyone know if it's possible ? And if anyone knows, I can really do with some help here... (I'm not a seasoned programmer...)
Here is what my M code looks like (from the PBI Query editor) to get to the point where I have the "groups" column isolated.
let Source = Sql.Databases("server.database.windows.net"), dbname = Source{[Name="dbname"]}[Data], dbo_insptemplate_all = dbname{[Schema="dbo",Item="insptemplate_all"]}[Data], #"Removed Columns" = Table.RemoveColumns(dbo_insptemplate_all,{"contextref", "name"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"rid"}), #"Parsed JSON" = Table.TransformColumns(#"Removed Duplicates",{{"data", Json.Document}}), #"Expanded data" = Table.ExpandRecordColumn(#"Parsed JSON", "data", {"groups", "columns"}, {"groups", "columns"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded data",{"columns"}) in #"Removed Columns1"
Any comments / help will be much appreciated.
Regards
Roelf
Solved! Go to Solution.
I actually ended up doing this "manually" by splitting the columns based on delimters (in my case "columns") to drop the columns part of the JSON. I then clean up the remaining object by replacing chars with "" and through a series of "split to rows" and more cleaning up I managed to get the data in the sequence i need. I have a feeling this is massively inefficient... but this particular table is used to reference values so it's quite small, so I'm hoping it won't have a big impact on performance.
try this - it should take the columns from JSON dynamically
let Source = Sql.Databases("server.database.windows.net"), dbname = Source{[Name="dbname"]}[Data], dbo_insptemplate_all = dbname{[Schema="dbo",Item="insptemplate_all"]}[Data], #"Removed Columns" = Table.RemoveColumns(dbo_insptemplate_all,{"contextref", "name"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"rid"}), #"Parsed JSON" = Table.TransformColumns(#"Removed Duplicates",{{"data", Json.Document}}), #"Expanded data" = Table.ExpandRecordColumn(#"Parsed JSON", "data", Table.ColumnNames([data]), Table.ColumnNames([data])), #"Removed Columns1" = Table.RemoveColumns(#"Expanded data",{"columns"}) in #"Removed Columns1"
Thanks for the reply @Stachu. Apologies for the late response but i've been away.
I tried the code (directly as is, with of course the correct dbname and server) - but I get the following error.
"Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"
I read up quite a bit on these commands, but I have to admit my knowledge on this is very lacking....
What is the output table I should expect from your commands ? Cause will this not give me the table with additional columns, a column per record entry in the JSON ?
As per my example - will my table not then end up having column names of "g1, g2, g3" etc ? If the next record have say values of g4, g5, g6, then my table will look like this:
g1 | g2 | g3 | g4 | g5 | g6 |
row 1 Null Null Null
row 2 null Null Null
My concern is that I wont be able to pivot that.
Thanks !
I actually ended up doing this "manually" by splitting the columns based on delimters (in my case "columns") to drop the columns part of the JSON. I then clean up the remaining object by replacing chars with "" and through a series of "split to rows" and more cleaning up I managed to get the data in the sequence i need. I have a feeling this is massively inefficient... but this particular table is used to reference values so it's quite small, so I'm hoping it won't have a big impact on performance.
try this - it should take the columns from JSON dynamically
let Source = Sql.Databases("server.database.windows.net"), dbname = Source{[Name="dbname"]}[Data], dbo_insptemplate_all = dbname{[Schema="dbo",Item="insptemplate_all"]}[Data], #"Removed Columns" = Table.RemoveColumns(dbo_insptemplate_all,{"contextref", "name"}), #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"rid"}), #"Parsed JSON" = Table.TransformColumns(#"Removed Duplicates",{{"data", Json.Document}}), #"Expanded data" = Table.ExpandRecordColumn(#"Parsed JSON", "data", Table.ColumnNames([data]), Table.ColumnNames([data])), #"Removed Columns1" = Table.RemoveColumns(#"Expanded data",{"columns"}) in #"Removed Columns1"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |