Convert XML ragged hierarchies to PBI Dataset

[Anonymous]

HI All,

I am loading XML data into PBI.  Each row has a different level of hierarchy.  When Power Query has "split" one level, it can't split the next level, because some data is now granular ( eg text) and some is still packed eg shows as [Table].

1. Is there are way to filter in the [Table] data so I can split it again?

OR 

2. Is there a hierarchical way the "split" command can recurse until it runs out of packed data?

OR

3. Any other way to deal with mixed-level hierarchies in Power Query?

Thanks

Geoff Speedy 

[Anonymous · Accepted solution]

Thanks Vanessa,

I solved it by creating 2 queries,  then combining them after splitting the second query twice.

I noticed that the offending rows (still showing [Table] after the first split) throw an error when you run text functions on them. So I forced an error with a text function,

= Table.AddColumn(#"Added Custom", "Body1", each try Text.Replace([Note Body],",","") otherwise null)

and saved the 2 sets of rows into separate tables

= Table.SelectRows(#"Added Custom1", each ([Body1] = null))

= Table.SelectRows(#"Added Custom1", each ([Body1] <> null))

At this point in the offending rows dataset, Power Query recognised the column can be split, so I split it again. 

Then I combined the new dataset with the old dataset.

Cheers

Geoff Speedy

[vanessafvg]

its much easier to interpet what you asking for when you provide sample data and what you expect to happen with that data.  Please provide more details, its very hard to respond to a conceptual post like this, sample data with your expected requirement is necessary.