XML Import - Table Type in Column

[brittany]

So, I have managed to import an XML via XML.Table, and have learned how to expand the columns in order to gain access to the data and apply it visually - however, there is a data condition that I just don't understand.

Some of the columns of resulting data contain cells with additional level of XML containing additional values.  These appear to be represented with the [table] designation - and if, within the Query Editor, I click on one of these, I can see the underlying data in a tiny table.  So, this data is certainly being understood and read correctly.

This is of course because multiple key:value entries are stored there, for example under the column named 'Parameters' there is a cell that contains a [Table] with the data: Color:Red and Size:M3  (but there could be any number of such elements).

I cannot expand the column in which these exist, because some of the cells have no data - and as such, the containing column does not provide an expand function. 

But, I do not really want to expand the data as it is too dynamic - I want to USE it.  How to I use this data?  

For example, in the Visual I would like to be able to show a listing of all rows in which, under column 'Parameters', color = red.

Or, create a Slicer that displays all of the possible colors from the column 'Parameters' as assigned to 'color'.

I have looked high and low, and just don't see any references or examples that talk about doing this with a cell of type 'Table'.

If someone could point me at a resource, book, post etc. that could help address this, that would be a big help.

Otherwise is it possible to add a helper column, and convert these various pairs of data into some type of Array that would be usable in this way?

Thank you  -B

[Pete_Murphy_71]

I am here at the end of 2022 and having the  same problem.

I have imported an XML file and have expanded a table. however some cells contain values, but other cells contain the text [Table]. When I click on the [Table] text I can see a series of values underneath (that appear in the bottom of the screen). 

But I can't work out how to expand the variour [table]s inside the cells...

[alwweb]

The XML import as well as the Parse transformation seem to be VERY particular about 1) Having a well formed XML document with a root, etc all defined. 2) Avoid having empty elements.  This seems to cause non-expandable tables inside of the expanded columns. 3) Avoid nesting elements.  This seems to be fixable by doing a double level (or deeper if necessary expand).

If anyone knows of a way to get rid of the empty [Table] references left by empty elements?  I really want to replace them with a NULL or a blank using M, but I'm just not sure how to do it.  Someone referenced a function that someone else created in another thread, but they also said it did not work.

I imported my XML from the Adventureworks database, the HumanResources.JobCandidate.Resume column.  I then used Parse > XML from the transform tab.  Skills expanded just fine.  Employment had nested elements, so the column needs to be further expanded before it can be used, which is fine once it is set up once, it will reapply the steps every time.  The problem column is Email.  Non existent elements are correctly translated into null which totally makes sense to me.  Empty Email elements are translated into empty tables which become [Table] in the reports.  I want the Tables to be changed to nulls as well. Any ideas?

[v-caliao-msft]

Hi @brittany,

Here are some useful links about parameters and slicer for you reference.
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
https://powerbi.microsoft.com/en-us/documentation/powerbi-service-tutorial-slicers/

If this is not what you want, please provide us some sample data and expected result, so that we can make further analysis.

Regards,

Charlie Liao

[sherryliu]

Hi Charlie,

I had the same problem Brittany had. Basically, all values for one of my column showed "Table". I looked at these reference you listed but it did not work for me. In the reference doc, when you expand the column with "Table" values in, it seems you can select the data values inside. However, when I tried to expand the column, it said Element:text. I could not find any source that helps to pull table data out. 

Here is my export data for this field in xml

 Here is what looks like in PowerBi Desktop

[lszymanski]

Hi Sherry,

Did you get an answer or figure this out?  I am trying to import an xml file as well and cannot get to the actual data.

Thank you,

LeAnn Szymanski

[stretcharm]

I've not had any issue with XML files.

Do you have a sample file I that shows the problem?

[brittany]

Hello, thanks.

I have looked at those, but am unable to get them working with my 'Table' data.  I'm not sure what I'm missing.

To be clear, this is an XML import, and working with a column that has 'Table' cells in it.  When I attempt to use this column, for example as a filter, all that appears is:

O (blank, i.e. nothing)

O [Table]

Inside the [Table] is the actual data, as described in my original post - but all I get is brackets with the word Table in the middle.  As stated originally, I presume I will need a helper column, but cannot find anywhere how to reference or extract data from a Table cell - on either the query side or the report side.  All I can do is look at it on the query side, as clicking on such a cell displays the contained data (successfully) underneath the grid.

Thanks