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
Anonymous
Not applicable

xml parent child into power bi as relational tables or a single flattened table

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.
 
 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @v-sihou-msft

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:

expandxmlchildtable.JPG

 

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.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

sample data to help with the problem:

<EstablishmentCollection>
    <EstablishmentDetail>
             <FHRSID>29021</FHRSID>
             <LocalAuthorityBusinessID>09/00046/COMM</LocalAuthorityBusinessID>
             <BusinessName>08 Express</BusinessName>
             <BusinessType>Takeaway/sandwich shop</BusinessType>
             <BusinessTypeID>7844</BusinessTypeID>
             <AddressLine1>256 Wickham Road Croydon Surrey CR0 8BJ</AddressLine1>
             <PostCode>CR0 8BJ</PostCode>
             <RatingValue>5</RatingValue>
             <RatingKey>fhrs_5_en-GB</RatingKey>
             <RatingDate>2011-04-07</RatingDate>
             <LocalAuthorityCode>507</LocalAuthorityCode>
             <LocalAuthorityName>Croydon</LocalAuthorityName>
             <LocalAuthorityWebSite>http://www.croydon.gov.uk</LocalAuthorityWebSite>
            <LocalAuthorityEmailAddress>food.safety@croydon.gov.uk</LocalAuthorityEmailAddress>
            <Scores>
                    <Hygiene>5</Hygiene>
                    <Structural>5</Structural>
                    <ConfidenceInManagement>5</ConfidenceInManagement>
            </Scores>
            <SchemeType>FHRS</SchemeType>
            <NewRatingPending>False</NewRatingPending>
            <Geocode>
                       <Longitude>-0.04707600000000</Longitude>
                       <Latitude>51.37497900000000</Latitude>
             </Geocode>
   </EstablishmentDetail>
<EstablishmentDetail>

@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"

66.PNG

 

 

Regards,

Anonymous
Not applicable

Hi @v-sihou-msft

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:

expandxmlchildtable.JPG

 

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.

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.