A bit more advanced JSON to Power BI

[SimonKibsgaard]

I am retrieving data from Firebase (JSON) and flatten this into a table. Hugoberry kindly helped me traverse the data for a very simple structure, but I encounter a new problem, when my structure gets a bit more complicated.

This is the JSON I receive:

{
  "AcmeCorp" : {
    "organizationname" : "Acme Corp.",
    "teams" : {
      "ATeam" : {
        "respondents" : {
          "16b7214e999774b488f8f41495104c69" : {
            "created" : "2017-02-20T09:38:52.817Z",
            "m" : "063a561d36e024e0e21f0d0a2d6dc143"
          },
          "34e2844033dca5ec651862b6b5ca9dfe" : {
            "created" : "2017-02-20T09:38:52.817Z",
            "m" : "96ffde757986ae10c937e2e3ae48b59b"
          }
        },
        "teamname" : "A Team"
      },
      "BTeam" : {
        "respondents" : {
          "d644c98e822fafe03e1a75025d7c40e8" : {
            "created" : "2017-02-20T09:38:52.817Z",
            "m" : "063a561d36e024e0e21f0d0a2d6dc143"
          }
        },
        "teamname" : "B Team"
      }
    }
  },
  "SimonInc" : {
    "organizationname" : "Simon Inc.",
    "teams" : {
      "All" : {
        "teamname" : "All",
        "respondents" : {
          "4611669c7aa9cd04727dde2c9bc96981" : {
            "created" : "2017-02-20T10:15:03.394Z",
            "m" : "6677de757986ae10c937e2e3ae484467"
          }
        }
      }
    }
  }
}

This is the table I would like (more or less):

Organization Team Respondent Created M

Organization	Team	Respondent	Created	M
Acme Corp.	A Team	16b7214e999774b488f8f41495104c69	2017-02-20T09:38:52.817Z	063a561d36e024e0e21f0d0a2d6dc143
Acme Corp.	A Team	34e2844033dca5ec651862b6b5ca9dfe	2017-02-20T09:38:52.817Z	96ffde757986ae10c937e2e3ae48b59b
Acme Corp.	B Team	d644c98e822fafe03e1a75025d7c40e8	2017-02-20T09:38:52.817Z	063a561d36e024e0e21f0d0a2d6dc143
Simon Inc.	All	4611669c7aa9cd04727dde2c9bc96981	2017-02-20T10:15:03.394Z	6677de757986ae10c937e2e3ae484467

The problem seems to be that I have both children and value pairs on a node, so I can't just expand all the way through without getting an error from Power BI.

I hope there's a JSON -> Power BI wiz that is able to help me.

Br, Simon

[hugoberry]

A combination of Table.ExpandRecordColumn and Table.ExpandListColumn

let
    json = Json.Document(File.Contents("D:\Downloads\power.json")),
    level0 = Record.ToTable(json),
    level0_exp = Table.ExpandRecordColumn(level0, "Value", {"organizationname", "teams"}, {"organizationname", "teams"}),
    level1_rec = Table.AddColumn(level0_exp, "teams_table", each Record.ToTable([teams])),
    level1_exp = Table.ExpandTableColumn(level1_rec, "teams_table", {"Name", "Value"}, {"teams.Name", "teams.Record"}),
    level1_exp_rec = Table.ExpandRecordColumn(level1_exp, "teams.Record", {"respondents", "teamname"}, {"respondents", "teamname"}),
    level2 = Table.AddColumn(level1_exp_rec, "Details", each Record.ToTable([respondents])[Value]),
    level2_exp_li = Table.ExpandListColumn(level2, "Details"),
    level2_exp_rec = Table.ExpandRecordColumn(level2_exp_li, "Details", {"created", "m"}, {"created", "m"})
in
    level2_exp_rec

[hugoberry]

Hi @SimonKibsgaard I've got inspired by your problem of converting JSON to table structures, so I've put together a JSON2table function. You can find the code here https://gist.github.com/Hugoberry/4ad49f4301edf47fffe2ef06aed61513

I've tested with both of your scenarios and it seems to work just fine.

Let me know if you encounter any problems with any other JSON structures 

[SimonKibsgaard]

Wow that is really cool @hugoberry and thank you for sharing your commented code. I will be using this for my next json input. Thanks again!

[hohlick · Accepted solution]

Hi @SimonKibsgaard

try:

let
    Source = "HERE_COMES_JSON_STRING", // insert you source string,
    #"Parsed JSON" = Json.Document(Source), // or here you can refer to the JSON as the Source
    toTable = Record.ToTable(#"Parsed JSON")[[Value]],
    #"Expanded {0}" = Table.ExpandRecordColumn(toTable, "Value", {"organizationname", "teams"}, {"Organization", "teams"}),
    Custom1 = Table.TransformColumns(#"Expanded {0}",{{"teams", Record.ToList}}),
    #"Expanded {0}1" = Table.ExpandListColumn(Custom1, "teams"),
    #"Expanded {0}2" = Table.ExpandRecordColumn(#"Expanded {0}1", "teams", {"teamname","respondents"}, {"Team", "respondents"}),
    #"Added Custom" = Table.AddColumn(#"Expanded {0}2", "R", each List.Transform(Record.FieldNames([respondents]), each [Resp = _])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "C", each List.Zip({[R],Record.FieldValues([respondents])})),
    #"Added Custom2" = Table.TransformColumns(#"Added Custom1", {{"C", each List.Transform(_, each Record.Combine(_))}}),
    #"Expanded {0}3" = Table.ExpandListColumn(#"Added Custom2", "C"),
    #"Expanded {0}4" = Table.ExpandRecordColumn(#"Expanded {0}3", "C", {"Resp", "created", "m"}, {"Respondent", "Created", "M"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}4",{"respondents", "R"})
in
    #"Removed Columns"

not optimal, but works

[SimonKibsgaard]

That really works!

Great, thnx @hohlick

br, Simon