Forum Discussion
Helper IA 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
| 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
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
5 Replies
hohlickContinued Contributor
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
- hugoberry
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- 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!
