Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roelf
Helper II
Helper II

JSON - Object info to table

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

1 ACCEPTED 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.

View solution in original post

4 REPLIES 4
Stachu
Community Champion
Community Champion

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"


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.

Stachu
Community Champion
Community Champion

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"

 

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.