Power Query show json value

Question

Hello I am not much familiar with the Power query. I am trying to extract value from JSON file.

JSON Data looks like this

{
  "data": [
    {
      "A": "AT",
      "B": [
        {
          "C": "CT",
          "D": "DT"
        },
        {
          "C": "ET",
          "D": "FT"
        }
      ],
      "E": "ET"
    },
    {
      "A": "FT",
      "B": [
        {
          "C": "GT",
          "D": "HT"
        },
        {
          "C": "IT",
          "D": "JT"
        }
      ],
      "E": "KT"
    }
  ]
}

In Power Query Editor This looks like this

What I am looking for is the way to show Value.B Column Data like this instead expending it to new row

user_7346922 · Answer

This is one way.

In the formula bar, type this below (you may need to replace Source with the name of your previous step):

= Table.TransformColumns(Source,{"Value.B", each Record.FieldValues(_)})

and press enter to get this:

Then, click the button at the top of the Value.B column to extract values and click Extract Values...

Then select --Custom-- in the dropdown and type ( and click OK to get this:

Then click Transform, Format, Add Suffix and type ) and click OK to get this:

Then click Transform, Group By and set it up like this:

and click OK to get this:

Then click Add Column, Custom Column and set it up like this:

and click OK to get this:

Then you can remove the AllData column and move the Value.B column to get this:

Here's my M code:

let
    Source = Table,
    #"Got Field Values" = Table.TransformColumns(Source,{"Value.B", each Record.FieldValues(_)}),
    #"Extracted Values" = Table.TransformColumns(#"Got Field Values", {"Value.B",  each Text.Combine(List.Transform(_, Text.From), "("), type text}),
    #"Added Suffix" = Table.TransformColumns(#"Extracted Values", {{"Value.B", each _ & ")", type text}}),
    #"Grouped Rows" = Table.Group(#"Added Suffix", {"Name", "Value.A", "Value.E"}, {{"AllData", each _, type table [Name=text, Value.A=text, Value.B=text, Value.E=text]}}),
    #"Added Value.B" = Table.AddColumn(#"Grouped Rows", "Value.B", each [AllData][Value.B]{0}&"#(lf)"&[AllData][Value.B]{1}),
    #"Removed Columns" = Table.RemoveColumns(#"Added Value.B",{"AllData"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Value.A", "Value.B", "Value.E"})
in
    #"Reordered Columns"

Power Query show json value

1 Answers