cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
roelf Regular Visitor
Regular Visitor

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

Accepted Solutions
roelf Regular Visitor
Regular Visitor

Re: JSON - Object info to table

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.

4 REPLIES 4
Super User
Super User

Re: JSON - Object info to table

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"

 

 

Super User
Super User

Re: JSON - Object info to table

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"
roelf Regular Visitor
Regular Visitor

Re: JSON - Object info to table

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 !

roelf Regular Visitor
Regular Visitor

Re: JSON - Object info to table

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.