Forum Discussion
Data Model Based on XML - Stuck
Hello,
I am trying to build a model based on some XML data extracted from a CRM via API (ie I have little control over the shape of the extracted data).
To summarise the data structure:
- each client is identifies with the following tags <client>[Client1 meta]</client>.
- inside the client, are the client activities.
- each activity is tagged by it's ID number (this is a unique ID, ie not repeated across clients.
ie the activity for each client is shown as follows:
<client>
<name>"Client1"</name>
<activities><id12345>....</id12345><id23456>...</id23456></activities>
</client>
<client>
<name>"Client2"</name>
<activities><id34567>....</id34567><id45678>...</id45678></activities>
</client>
When I bring this into PBI, all of the data ends up as nested tables. This would be fine, except that because <activites> exists within each client, I end up with the attached image, where each row is an <idxxxxx> and each column is a different client - there are thousands of each. Is there a way of "concatenating" these columns so that my nested tables all exist in a single column (thereby making it easier to expand and manipulate from there)?
I have created some dummy data and a PBIX to show what I mean more easily - Link.
TIA
let Source = "<Response>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client1""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id12345>#(cr)#(lf)<id>""12345""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 12345""</activityinfo>#(cr)#(lf)</id12345>#(cr)#(lf)<id23456>#(cr)#(lf)<id>""23456""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 23456""</activityinfo>#(cr)#(lf)</id23456>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client2""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id34567>#(cr)#(lf)<id>""34567""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 34567""</activityinfo>#(cr)#(lf)</id34567>#(cr)#(lf)<id45678>#(cr)#(lf)<id>""45678""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 45678""</activityinfo>#(cr)#(lf)</id45678>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client3""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id56789>#(cr)#(lf)<id>""56789""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 56789""</activityinfo>#(cr)#(lf)</id56789>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client4""</name>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client5""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id678910>#(cr)#(lf)<id>""678910""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 678910""</activityinfo>#(cr)#(lf)</id678910>#(cr)#(lf)<id78910>#(cr)#(lf)<id>""78910""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 78910""</activityinfo>#(cr)#(lf)</id78910>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)</Response>", #"Parse XML" = Xml.Tables(Source), #"Expanded Table" = Table.ExpandTableColumn(#"Parse XML", "Table", {"name", "activities"}), #"Transformed activities" = Table.TransformColumns(#"Expanded Table", {"activities", each let hdr=Table.ColumnNames(_) in try Table.Unpivot(_,hdr,"id","content") otherwise #table({"id","content"},{})}), #"Expanded activities" = Table.ExpandTableColumn(#"Transformed activities", "activities", {"content"}, {"content"}), #"Expanded content" = Table.ExpandTableColumn(#"Expanded activities", "content", {"id", "activityinfo"}) in #"Expanded content"
2 Replies
let Source = "<Response>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client1""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id12345>#(cr)#(lf)<id>""12345""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 12345""</activityinfo>#(cr)#(lf)</id12345>#(cr)#(lf)<id23456>#(cr)#(lf)<id>""23456""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 23456""</activityinfo>#(cr)#(lf)</id23456>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client2""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id34567>#(cr)#(lf)<id>""34567""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 34567""</activityinfo>#(cr)#(lf)</id34567>#(cr)#(lf)<id45678>#(cr)#(lf)<id>""45678""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 45678""</activityinfo>#(cr)#(lf)</id45678>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client3""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id56789>#(cr)#(lf)<id>""56789""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 56789""</activityinfo>#(cr)#(lf)</id56789>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client4""</name>#(cr)#(lf)</client>#(cr)#(lf)<client>#(cr)#(lf)<name>""Client5""</name>#(cr)#(lf)<activities>#(cr)#(lf)<id678910>#(cr)#(lf)<id>""678910""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 678910""</activityinfo>#(cr)#(lf)</id678910>#(cr)#(lf)<id78910>#(cr)#(lf)<id>""78910""</id>#(cr)#(lf)<activityinfo>""here is some info about activity 78910""</activityinfo>#(cr)#(lf)</id78910>#(cr)#(lf)</activities>#(cr)#(lf)</client>#(cr)#(lf)</Response>", #"Parse XML" = Xml.Tables(Source), #"Expanded Table" = Table.ExpandTableColumn(#"Parse XML", "Table", {"name", "activities"}), #"Transformed activities" = Table.TransformColumns(#"Expanded Table", {"activities", each let hdr=Table.ColumnNames(_) in try Table.Unpivot(_,hdr,"id","content") otherwise #table({"id","content"},{})}), #"Expanded activities" = Table.ExpandTableColumn(#"Transformed activities", "activities", {"content"}, {"content"}), #"Expanded content" = Table.ExpandTableColumn(#"Expanded activities", "content", {"id", "activityinfo"}) in #"Expanded content"You've nailed it, thanks.
I've been scratching my head for a week!
