Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TA9
Frequent Visitor

transpose? or combining data labels

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, 

 

TA9_0-1680189016750.png

 

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?

 

wddisabilityobservations.observation
AcklamDoes not apply0
AcklamDisabled under the Equality Act: Day-to-day activities limited a lot378
AcklamDisabled under the Equality Act: Day-to-day activities limited a little549
AcklamNot disabled under the Equality Act: Has long-term physical or mental health condition but day-to-day activities are not limited386
AcklamNot disabled under the Equality Act: No long-term physical or mental health conditions4585

 

Thanks

 

1 ACCEPTED SOLUTION
TA9
Frequent Visitor

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 😀

View solution in original post

1 REPLY 1
TA9
Frequent Visitor

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 😀

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors