Hi all, first time poster, so take it easy on me!
I'm trying to extract specific columns from an XML relational database I imported into Excel (owing to my utter lack of Access knowledge and general ineptitude with databases). I'm getting a bunch of nested tables that I can't seem to resolve by unpivoting the columns in Query Editor/Power Query, even repeatedly. I just keep getting duplicate columns
This is a screenshot of what I have. The first column is the drug ID, the second is either a nested table or a simple text, depending if the drug has multiple groups (approved, investigational, etc). One ID can have up to 6 groups.
EXCEL_2016-08-10_15-18-04.png
EXCEL_2016-08-10_15-21-13.png
What I want is an output of each group associated with an ID on separate rows, like the following:
ID Group Many other columns of data DB00003 Approved Many other columns of data DB00004 Approved Many other columns of data DB00004 Investigational Many other columns of data DB00005 Approved Many other columns of data DB00006 Special Snowflake Many other columns of data
Is there a feature I'm missing, or another approach I can take? I'm pretty stumped at this point, so any suggestions are appreciated!
EDIT: I've also attached excerpts from the original XML file and workbook. The original has 100k+ rows.
sample.xlsx
sample (2).zip
Bookmarks