Forum Discussion

SimonKibsgaard's avatar
SimonKibsgaard
Icon for Helper I rankHelper I
9 years ago
Solved

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 get...
Need Help
  • hohlick's avatar
    hohlick
    9 years ago

    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

     

     

hugoberry's avatar
hugoberry
Icon for Responsive Resident rankResponsive Resident
9 years ago

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

 

  • SimonKibsgaard's avatar
    SimonKibsgaard
    Icon for Helper I rankHelper I
    9 years ago

    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!