Forum Discussion
XML input - how to handle missing tables
I have an XML file that contains multiple tables. Unfortunately the external system excludes tables (and columns) from the XML if they have no data, so this breaks my import in Power Query:
let Source = Xml.Tables(File.Contents(SavedXML)), works fine
Optional_Table = Source{[Name="Optional_Table"]}[Table], breaks
I'm using Table.SelectColumns with MissingField.UseNull to handle missing columns, but haven't found a way to do something similar if the entire table is missing.
What should happen is that the the table gets created in Power Query, but without any row (or a dummy row if needed). Is that achievable?
I think I found a solution. I created a dummy query that holds the optional XML elements. I then appended the dummy query....
let
Source = Xml.Tables(File.Contents(SavedXML)),
#"Appended Query" = Table.Combine({Source, Dummy_XML}),...which worked if the XML was missing. If the XML already had the element, I ended up with a duplicate row that I removed using
#"Removed Duplicates" = Table.Distinct(#"Appended Query",{"Name"}),Seems to do the job.
2 Replies
Hi AlexHeinze ,
Are you importing an empty table as shown below?
Perhaps you can select the "Source" step in the already imported table and then you will be able to see the empty table.
Best Regards,
WinnizI think I found a solution. I created a dummy query that holds the optional XML elements. I then appended the dummy query....
let
Source = Xml.Tables(File.Contents(SavedXML)),
#"Appended Query" = Table.Combine({Source, Dummy_XML}),...which worked if the XML was missing. If the XML already had the element, I ended up with a duplicate row that I removed using
#"Removed Duplicates" = Table.Distinct(#"Appended Query",{"Name"}),Seems to do the job.
