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

A bit more advanced JSON to Power BI

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

OrganizationTeamRespondentCreatedM
Acme Corp.  A Team 16b7214e999774b488f8f41495104c69  2017-02-20T09:38:52.817Z 063a561d36e024e0e21f0d0a2d6dc143
Acme Corp.A Team34e2844033dca5ec651862b6b5ca9dfe2017-02-20T09:38:52.817Z96ffde757986ae10c937e2e3ae48b59b
Acme Corp.B Teamd644c98e822fafe03e1a75025d7c40e82017-02-20T09:38:52.817Z063a561d36e024e0e21f0d0a2d6dc143
Simon Inc.All4611669c7aa9cd04727dde2c9bc969812017-02-20T10:15:03.394Z6677de757986ae10c937e2e3ae484467

 

 

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

1 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

 

 

Maxim Zelensky
excel-inside.pro

View solution in original post

5 REPLIES 5
hugoberry
Responsive Resident
Responsive Resident

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

 

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 

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!

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

 

 

Maxim Zelensky
excel-inside.pro

That really works!

Great, thnx @hohlick

 

br, Simon

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.