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
scarr4
Frequent Visitor

Get API data in XML format and transform

Hello there, anyone have luck pulling data froma web api (that comes in in XML nested format) and turning it into the right data table for building reports with?

The data looks like this:

 <reportDataDTO>
        <childReports>
            <childReport>
                <dataRows>
                    <dataRow>
                        <entries>
                            <entry>
                                <attributeName>lradName</attributeName>
                                <dataValue>AP21</dataValue>
                                <displayName>AP Name</displayName>
                            </entry>
                            <entry>
                                <attributeName>ipAddress_address</attributeName>
                                <dataValue>10.1.1.1</dataValue>
                                <displayName>AP IP Address</displayName>
                            </entry>
                            <entry>
                                <attributeName>macAddress</attributeName>
                                <dataValue>21:23:d4:e4:11:50</dataValue>
                                <displayName>Base Radio MAC</displayName>
                            </entry>
                            <entry>
                                <attributeName>apNeighborName</attributeName>
                                <dataValue>Cat21.domain.lcl</dataValue>
                                <displayName>Neighbor Name</displayName>
                            </entry>
                            <entry>
                                <attributeName>apNeighborPort</attributeName>
                                <dataValue>FastEthernet0/10</dataValue>
                                <displayName>Neighbor Port</displayName>
                            </entry>
                        </entries>
                    </dataRow>

              </dataRows>

            </childReport>
        </childReports>
    </reportDataDTO>

 

no matter how I split out the data and unpivot or change I can't get the "dataRow" info to show up as rows. I get to a point that I can get "entry" to be a column with each row showing as "Table" and then if I highlight a row with "Table" in it I can see the data but it is by row not by column. I need either the attributeName or the displayName values to be the column headers with the dataValue values to be the data (if that makes sense). Seems obvious from the XML since "entries" is a row of data so any help with the transform would be welcome

thanks

Steve

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

@scarr4,

When I directly copy the above data in a text file,named it as test.xml file and connect to it in Power BI Desktop, I get the following table. 

1.JPG

Do you want to get the following expected result?
2.JPG

If so, you can use Transpose feature. Add a blank query, copy and paste the following code to test it, remember to replace yourfolder with your own folder name.

let
    Source = Xml.Tables(File.Contents("yourfolder\test.xml")),
    Table0 = Source{0}[Table],
    Table1 = Table0{0}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    Table4 = Table3{0}[Table],
    Table5 = Table4{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table5,{{"attributeName", type text}, {"dataValue", type text}, {"displayName", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"displayName"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"lradName", type text}, {"ipAddress_address", type text}, {"macAddress", type text}, {"apNeighborName", type text}, {"apNeighborPort", type text}})
in
    #"Changed Type1"



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yuezhe-msft
Employee
Employee

@scarr4,

When I directly copy the above data in a text file,named it as test.xml file and connect to it in Power BI Desktop, I get the following table. 

1.JPG

Do you want to get the following expected result?
2.JPG

If so, you can use Transpose feature. Add a blank query, copy and paste the following code to test it, remember to replace yourfolder with your own folder name.

let
    Source = Xml.Tables(File.Contents("yourfolder\test.xml")),
    Table0 = Source{0}[Table],
    Table1 = Table0{0}[Table],
    Table2 = Table1{0}[Table],
    Table3 = Table2{0}[Table],
    Table4 = Table3{0}[Table],
    Table5 = Table4{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table5,{{"attributeName", type text}, {"dataValue", type text}, {"displayName", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"displayName"}),
    #"Transposed Table" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"lradName", type text}, {"ipAddress_address", type text}, {"macAddress", type text}, {"apNeighborName", type text}, {"apNeighborPort", type text}})
in
    #"Changed Type1"



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for this solution.  If I wanted to keep the live connection to the api, how would I accomplish this?

 

ah, thank you. I think I was getting lost on the "promoted headers" step. Works like a charm. thanks Steve

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