CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktopdata-cleaningm
Nick
Nick

Reputation: 77

Power Query: Structured record while converting to JSON

I have a power query record as follows:

Data = [
        tracker = {
            [
                foo = {
                        [
                            field_a= "Something",
                            field_b = "data2"
                        ],
                        [
                            field_a= "Something",
                            field_c = "data2"
                        ]
                },
                bar = "Data"
            ],
    
            [
                foo = {
                        [
                            field_c= "Something",
                            field_b = "data2"
                        ],
                        [
                            field_a= "Something",
                            field_c = "data2"
                        ]
                },
                bar = "Data2"
            ]
        } 
    ]

When I convert it into a JSON, I obtain this:

{
  "tracker": [
    {
      "foo": [
        {
          "field_a": "Something",
          "field_b": "data2"
        },
        {
          "field_a": "Something",
          "field_c": "data2"
        }
      ],
      "bar": "Data"
    },
    {
      "foo": [
        {
          "field_c": "Something",
          "field_b": "data2"
        },
        {
          "field_a": "Something",
          "field_c": "data2"
        }
      ],
      "bar": "Data2"
    }
  ]
}

I need the foo array to have structured objects within it, in the sense that each object should have field_a, field_b, field_c in it. Example:

foo = {
        [
            field_c= "Something",
            field_b = "data2",
            field_a = null
        ],
        [
            field_a= "Something",
            field_c = "data2".
            field_b = null
        ]
    }

I tried using

List.Transform(x, each Record.TransformFields(_, {
        { "field_a", Text.Trim },
        { "field_b", Text.Trim },
        { "field_c", Text.Trim }
    }, MissingField.UseNull))

however, I am losing reference to the bar field in the final output. Any help would be appreciated.

Upvotes: 1

Views: 128

Answers (2)

horseyride
horseyride

Reputation: 21428

Try this in powerquery

It splits on } then checks for the fields defined in the first row, and if one or two of them are missing, adds them, then puts the pieces back together

let TableWithPhrases = Table.Buffer(#table({"Keyword"}, {{"field_a"}, {"field_b"}, {"field_c"}})),

Source =  [tracker = {[foo = {[
                        field_a= "Something",
                        field_b = "data2"
                    ],
                    [
                        field_a= "Something",
                        field_c = "data2"
                    ]
            },
            bar = "Data"
        ],

        [
            foo2 = {
                    [
                        field_c= "Something",
                        field_b = "data2"
                    ],
                    [
                        field_a= "Something",
                        field_c = "data2"
                    ]
            },
            bar = "Data2"
        ]
    } 
],
prior =Text.FromBinary(Json.FromValue(Source[tracker])), 
Splitit=Text.Split(prior,"}"),
#"Converted to Table" = Table.FromList(Splitit, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Select(TableWithPhrases[Keyword], (x) => not(Text.Contains([Column1], x)))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if List.Count([Custom])=Table.RowCount(TableWithPhrases) then "" else Text.Combine(List.Transform([Custom], each ","""&_&""":"""""))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Column1]&[Custom.1]),
final = Text.Combine(#"Added Custom2"[Custom.2],"}")
in  final

Before

[{"foo":[{"field_a":"Something","field_b":"data2"},{"field_a":"Something","field_c":"data2"}],"bar":"Data"},{"foo2":[{"field_c":"Something","field_b":"data2"},{"field_a":"Something","field_c":"data2"}],"bar":"Data2"}]

After

[{"foo":[{"field_a":"Something","field_b":"data2","field_c":""},{"field_a":"Something","field_c":"data2","field_b":""}],"bar":"Data"},{"foo2":[{"field_c":"Something","field_b":"data2","field_a":""},{"field_a":"Something","field_c":"data2","field_b":""}],"bar":"Data2"}]

Upvotes: 2

davidebacci
davidebacci

Reputation: 30344

This seems to work for me.

let
    Data = [
        tracker = {
            [
                foo = {
                        [
                            field_a= "Something",
                            field_b = "data2"
                        ],
                        [
                            field_a= "Something",
                            field_c = "data2"
                        ]
                },
                bar = "Data"
            ],
    
            [
                foo = {
                        [
                            field_c= "Something",
                            field_b = "data2"
                        ],
                        [
                            field_a= "Something",
                            field_c = "data2"
                        ]
                },
                bar = "Data2"
            ]
        } 
    ],
    #"Converted to Table" = Record.ToTable(Data),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", 
    each let 
        a = [Value],
        b = List.Transform(a, each Record.TransformFields(_, {
            { "foo", (x)=> List.Transform(x, (y)=> Record.TransformFields(y, {
            { "field_a", Text.Trim },
            { "field_b", Text.Trim },
            { "field_c", Text.Trim }
        }, MissingField.UseNull) )}
            
        }))
    in b)
in
    #"Added Custom"

Upvotes: 2

Related Questions