Forum Discussion
Solved
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...
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"
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!
