Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I am looking at the following xml data:
http://ratings.food.gov.uk/OpenDataFiles/FHRS507en-GB.xml
I am interested to either flatten the child data into a single table in power bi along the parent data or create relational tables for the child data. There seems to be two chld elements within the parent: Scores and Geocode.
Are either of the options an easy task within the m query?
Thank you.
Solved! Go to Solution.
Thank you for replying to this post. maybe i was not clear on what i wanted to achieve. Your power query code is auto generated when we select to open the source url. Within your table2 there are 3 child data (rating date, Scores, Geocode) - I wanted the data in these tables (child xml nodes) flattened and expanded out.
I see now that this is actually a very easy process, one just needs to click on the expand icon on the right side of the header for the table column:
the problem is solved for me by the following m query:
let
Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS507en-GB.xml")),
Table1 = Source{1}[Table],
Table0 = Table1{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"FHRSID", Int64.Type}, {"LocalAuthorityBusinessID", type text}, {"BusinessName", type text}, {"BusinessType", type text}, {"BusinessTypeID", Int64.Type}, {"AddressLine1", type text}, {"PostCode", type text}, {"RatingValue", type text}, {"RatingKey", type text}, {"LocalAuthorityCode", Int64.Type}, {"LocalAuthorityName", type text}, {"LocalAuthorityWebSite", type text}, {"LocalAuthorityEmailAddress", type text}, {"SchemeType", type text}, {"NewRatingPending", type logical}}),
#"Expanded Scores" = Table.ExpandTableColumn(#"Changed Type", "Scores", {"Hygiene", "Structural", "ConfidenceInManagement"}, {"Scores.Hygiene", "Scores.Structural", "Scores.ConfidenceInManagement"}),
#"Expanded Geocode" = Table.ExpandTableColumn(#"Expanded Scores", "Geocode", {"Longitude", "Latitude", "Element:Text"}, {"Geocode.Longitude", "Geocode.Latitude", "Geocode.Element:Text"}),
#"Expanded RatingDate" = Table.ExpandTableColumn(#"Expanded Geocode", "RatingDate", {"Element:Text"}, {"RatingDate.Element:Text"})
in
#"Expanded RatingDate"
Thank you for your help and support.
sample data to help with the problem:
@Anonymous
In this scenario, you can use Power Query to get retrieve the data from XML into a single table.
let Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS507en-GB.xml")), Table = Source{1}[Table], Table2 = Table{0}[Table], #"Changed Type" = Table.TransformColumnTypes(Table2,{{"FHRSID", Int64.Type}, {"LocalAuthorityBusinessID", type text}, {"BusinessName", type text}, {"BusinessType", type text}, {"BusinessTypeID", Int64.Type}, {"AddressLine1", type text}, {"PostCode", type text}, {"RatingValue", type text}, {"RatingKey", type text}, {"LocalAuthorityCode", Int64.Type}, {"LocalAuthorityName", type text}, {"LocalAuthorityWebSite", type text}, {"LocalAuthorityEmailAddress", type text}, {"SchemeType", type text}, {"NewRatingPending", type logical}}) in #"Changed Type"
Regards,
Thank you for replying to this post. maybe i was not clear on what i wanted to achieve. Your power query code is auto generated when we select to open the source url. Within your table2 there are 3 child data (rating date, Scores, Geocode) - I wanted the data in these tables (child xml nodes) flattened and expanded out.
I see now that this is actually a very easy process, one just needs to click on the expand icon on the right side of the header for the table column:
the problem is solved for me by the following m query:
let
Source = Xml.Tables(Web.Contents("http://ratings.food.gov.uk/OpenDataFiles/FHRS507en-GB.xml")),
Table1 = Source{1}[Table],
Table0 = Table1{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"FHRSID", Int64.Type}, {"LocalAuthorityBusinessID", type text}, {"BusinessName", type text}, {"BusinessType", type text}, {"BusinessTypeID", Int64.Type}, {"AddressLine1", type text}, {"PostCode", type text}, {"RatingValue", type text}, {"RatingKey", type text}, {"LocalAuthorityCode", Int64.Type}, {"LocalAuthorityName", type text}, {"LocalAuthorityWebSite", type text}, {"LocalAuthorityEmailAddress", type text}, {"SchemeType", type text}, {"NewRatingPending", type logical}}),
#"Expanded Scores" = Table.ExpandTableColumn(#"Changed Type", "Scores", {"Hygiene", "Structural", "ConfidenceInManagement"}, {"Scores.Hygiene", "Scores.Structural", "Scores.ConfidenceInManagement"}),
#"Expanded Geocode" = Table.ExpandTableColumn(#"Expanded Scores", "Geocode", {"Longitude", "Latitude", "Element:Text"}, {"Geocode.Longitude", "Geocode.Latitude", "Geocode.Element:Text"}),
#"Expanded RatingDate" = Table.ExpandTableColumn(#"Expanded Geocode", "RatingDate", {"Element:Text"}, {"RatingDate.Element:Text"})
in
#"Expanded RatingDate"
Thank you for your help and support.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |