My XML file has multiple tables that I have been able to expand. However when a record within one of the expanded table columns has another table, instead of the value, I don't know how to expand it for the whole column. I am unable to filter that column based on the value "table" as that is not a value that appears in the filter.
My data consists of student records that contains a Program Eligibility table, with one column containing a program code. A student may be eligible for more than one program in which case the expanded table column reports some cells with "table" as opposed to the eligibility code. How do I expand each of those "nested tables" for those records that have them?
Hi Cheryl,
Each case is different and needs different solutions. Can you upload a sample xml file?
Unfortunately I cannot share a sample .xml because it contains too much personal data that I am not able to share, and recreating this would be challenging as I receive the .xml files from another source and don't create them myself. I can include some screen shots here that may be helpful to explain.
I can expand most columns without a problem and get one record per row within the columns. However the "Program Participation" column causes problems because sometimes it includes several "records" for each row and that is when I have trouble and don't know how to expand those tables in all of the cells. They are basically nested and I can't filter just those cells either. as they don't show up in my filter, except as an error if I change the data type.
You can try moving the tables into another column, by adding another column with this formula:
=if Value.Is([ColumnName],type table)=true then [ColumnName] else null
You should be able to expand the new column, as it will have a single data type, tables.
Or, if this is enough for you, that table can be converted to a text string with all the values.
Works! thank yu!!!!
