transpose? or combining data labels

[TA9]

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

[TA9 · Accepted 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 😀