Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have multiple XML files witn variety of data stored in them.
i need to create tables based on the node names. The data contained in a different nodes of XML file is quite different
here is an example of data:
<BIMListDS xmlns="http://tempuri.org/BIMListDS.xsd"> <DatabaseInfo> <ID>a71e1eb0-9811-448c-952c-c5f99d197bfd</ID> <SchemaVersion>3</SchemaVersion> <Name>01 PW Imperial 2016</Name> <LastUpdatedDate>2016-05-24T14:29:06.8645433</LastUpdatedDate> <IsRemote>true</IsRemote> <RemoteServerAddress>net.tcp://PW02BIMList:5055/BIMListDataService</RemoteServerAddress> <IsCloud>false</IsCloud> <CreatedBy>46d418f4-a599-44ee-9a12-72a39b123d20</CreatedBy> <LastUpdateBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</LastUpdateBy> </DatabaseInfo> <User> <ID>46d418f4-a599-44ee-9a12-72a39b123d20</ID> <Name>gorinah</Name> </User> <User> <ID>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</ID> <Name>gorina_admin</Name> </User> <User> <ID>cd195d56-5603-4d97-a38f-baeecc3be4a0</ID> <Name>lopezb</Name> </User> <User> <ID>58c53056-67e8-41ac-980d-d75586b12c13</ID> <Name>moorej</Name> </User> <User> <ID>d269e3ba-163e-4903-8cba-07c520dc0046</ID> <Name>clinea</Name> </User> <User> <ID>c26b59d7-bb5b-4213-a8d6-06cc89c7c7a8</ID> <Name>hamiltonad</Name> </User> <ContentItem> <ID>22389006-df18-419f-a657-d005bb31d642</ID> <Name>Graphic-Cross</Name> <CategoryID>-2000279</CategoryID> <Path>S:\_Firmwide\BIMListContent\2016-Imperial\_Hatches(RVT).rvt</Path> <IsFamily>false</IsFamily> <IsVisible>true</IsVisible> <AverageRating>0</AverageRating> <TotalLoadCount>0</TotalLoadCount> <AddedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</AddedBy> <LastUpdatedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</LastUpdatedBy> <AddedDate>2016-05-09T11:57:38.3246744-05:00</AddedDate> <LastUpdatedDate>2016-05-09T11:57:38.3246744-05:00</LastUpdatedDate> <ElementId>3215</ElementId> <IsOffline>false</IsOffline> </ContentItem> <ContentItem> <ID>d36f216f-6989-4417-8668-9c52dfc923ec</ID> <Name>Graphic-Crosshatch - Large</Name> <CategoryID>-2000279</CategoryID> <Path>S:\_Firmwide\BIMListContent\2016-Imperial\_Hatches(RVT).rvt</Path> <IsFamily>false</IsFamily> <IsVisible>true</IsVisible> <AverageRating>0</AverageRating> <TotalLoadCount>0</TotalLoadCount> <AddedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</AddedBy> <LastUpdatedBy>5ad2d00c-e4d7-4c64-8d01-2a410940b9e2</LastUpdatedBy> <AddedDate>2016-05-09T11:57:43.9126149-05:00</AddedDate> <LastUpdatedDate>2016-05-09T11:57:43.9126149-05:00</LastUpdatedDate> <ElementId>3231</ElementId> <IsOffline>false</IsOffline> </ContentItem>
I need to create tables based on the node names. the number of nodes of each type varies
I have followed the directions from the following post: http://community.powerbi.com/t5/Desktop/Import-multiple-XML-files/m-p/8900/highlight/true#M1596
it helped a lot, thanks!
however i need to select just some node types, and want to base it on the name
the function
(path as text) => let Source = Xml.Tables(File.Contents(path)), Table0 = Source{0}[Table], #"Changed Type" = Table.TransformColumnTypes(Table0,{{"ID", type text}, {"SchemaVersion", Int64.Type}, {"Name", type text}, {"LastUpdatedDate", type datetime}, {"IsRemote", type logical}, {"RemoteServerAddress", type text}, {"IsCloud", type logical}}) in #"Changed Type"
allows me to traverse the xml file and get the tables sequentually, but I am trying to select only nodes I am interested in.
many thanks for your help
Solved! Go to Solution.
Not quite sure about your requirement, if you'd like to load the elements table by name but not sequentially(0,1,2 etc), you can try
let Source = Xml.Tables(File.Contents("C:\Users\user1\Desktop\1.XML")), Table2 = Source{[Name="User"]}[Table] in Table2
Not quite sure about your requirement, if you'd like to load the elements table by name but not sequentially(0,1,2 etc), you can try
let Source = Xml.Tables(File.Contents("C:\Users\user1\Desktop\1.XML")), Table2 = Source{[Name="User"]}[Table] in Table2
thanks!
It worked.
perhaps I need to clarify the task:
in multiple XMLs I am working with, some tables exist in all of them, and some tables exist in just some XMLs. Also, the table sequence order is not the same in some of them. that is why it was omportatn to get them by name
many thanks again:)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
69 | |
68 | |
43 |
User | Count |
---|---|
148 | |
104 | |
102 | |
89 | |
66 |