XML
Item | Description |
---|---|
Release State | General Availability |
Products | Excel Power BI (Semantic models) Power BI (Dataflows) Fabric (Dataflow Gen2) Power Apps (Dataflows) Dynamics 365 Customer Insights Analysis Services |
Function Reference Documentation | Xml.Tables Xml.Document |
Note
Some capabilities may be present in one product but not others due to deployment schedules and host-specific capabilities.
- Import
To load a local XML file:
Select the XML option in the Get Data selection. This action will launch a local file browser and allow you to select your XML file.
Browse to the directory containing the local XMl file you want to load, and then select Open.
In Navigator, select the data you want, and then either select Load to load the data, or Transform Data to continue transforming the data in Power Query Editor.
To load a local XML file:
From the Data sources page, select XML.
Enter the path to the local XML file.
Select an on-premises data gateway from Data gateway.
If authentication is required, enter your credentials.
Select Next.
Loading the XML file will automatically launch the Power Query Editor. From the editor, you can then transform the data if you want, or you can just save and close to load the data.
If you want to load an XML file from the web, instead of selecting the XML connector you can select the Web connector. Paste in the address of the desired file and you'll be prompted with an authentication selection, since you're accessing a website instead of a static file. If there's no authentication, you can just select Anonymous. As in the local case, you'll then be presented with the table that the connector loads by default, which you can Load or Transform.
Due to the fact that many XML documents have ragged or nested data, you may have to do extra data shaping to get it in the sort of form that will make it convenient to do analytics. This holds true whether you use the UI accessible Xml.Tables
function, or the Xml.Document
function. Depending on your needs, you may find you have to do more or less data shaping.
If your document contains a mixture of text and non-text sibling nodes, you may encounter issues.
For example if you have a node like this:
<abc>
Hello <i>world</i>
</abc>
Xml.Tables
will return the "world" portion but ignore "Hello". Only the element(s) are returned, not the text. However, Xml.Document
will return "Hello <i>world</i>". The entire inner node is turned to text, and structure isn't preserved.