Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I am connecting to the ONS open source API in Power BI using the information below:
Source = Json.Document(Web.Contents("insert api link here because it won't let me post")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded observations" = Table.ExpandListColumn(#"Converted to Table", "observations"),
#"Expanded observations1" = Table.ExpandRecordColumn(#"Expanded observations", "observations", {"dimensions", "observation"}, {"observations.dimensions", "observations.observation"}),
#"Expanded observations.dimensions" = Table.ExpandListColumn(#"Expanded observations1", "observations.dimensions"),
#"Expanded observations.dimensions1" = Table.ExpandRecordColumn(#"Expanded observations.dimensions", "observations.dimensions", {"dimension", "dimension_id", "option", "option_id"}, {"observations.dimensions.dimension", "observations.dimensions.dimension_id", "observations.dimensions.option", "observations.dimensions.option_id"}),
#"Expanded links" = Table.ExpandRecordColumn(#"Expanded observations.dimensions1", "links", {"self"}, {"links.self"}),
#"Expanded links.self" = Table.ExpandRecordColumn(#"Expanded links", "links.self", {"href"}, {"links.self.href"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded links.self",{{"observations.dimensions.dimension", type text}, {"observations.dimensions.dimension_id", type text}, {"observations.dimensions.option", type text}, {"observations.dimensions.option_id", type text}, {"observations.observation", Int64.Type}, {"links.self.href", type text}, {"total_observations", Int64.Type}, {"blocked_areas", Int64.Type}, {"total_areas", Int64.Type}, {"areas_returned", Int64.Type}})
in
#"Changed Type"
The data comes back as shown in the table below,
how do I add a data manipulation row in so that observations.dimensions.dimension_id is transposed to two fields (wd and disability) so it looks like the table below?
wd | disability | observations.observation |
Acklam | Does not apply | 0 |
Acklam | Disabled under the Equality Act: Day-to-day activities limited a lot | 378 |
Acklam | Disabled under the Equality Act: Day-to-day activities limited a little | 549 |
Acklam | Not disabled under the Equality Act: Has long-term physical or mental health condition but day-to-day activities are not limited | 386 |
Acklam | Not disabled under the Equality Act: No long-term physical or mental health conditions | 4585 |
Thanks
Solved! Go to Solution.
Did a messy fix by adding an index field after #"Expanded observations1" and then duplicating the tables after all the steps, filtered on wd in one table and disability in the other and then joined via the index... it's not perfect but it works 😀
Did a messy fix by adding an index field after #"Expanded observations1" and then duplicating the tables after all the steps, filtered on wd in one table and disability in the other and then joined via the index... it's not perfect but it works 😀