Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm hoping to expand some json into a table but because of the way the json is structured, the values I need end up as column headers instead of row values. Has anyone run into this before?
Example of json input:
What happens in PowerBI:
Desired end state of query should result in a table like this:
Additionally, if I expand the "50" column then the "50" is wiped out by the next record "101" which is in turn wiped out by "140" etc down the line. Renaming the expanded column doesn't help. Power Query:
let
Source = Json.Document(Web.Contents("APIurl")),
data = Source[data],
attendance = data[attendance],
#"Converted to Table" = Record.ToTable(attendance),
#"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"50"}, {"50"}),
#"Expanded 50" = Table.ExpandRecordColumn(#"Expanded Value", "50", {"101"}, {"101"}),
#"Expanded 101" = Table.ExpandRecordColumn(#"Expanded 50", "101", {"140"}, {"140"})
in
#"Expanded 101"
Does anyone know a way to achieve this? Is this just bad json?
Thank you,
UIT
Hello @cboake
were you able to solve the problem with any reply given?
If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
All the best
Jimmy
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.