XML Source. Column contains Table but cant expand this column

[DebbieE]

Hi,

Im importing XML Files. Ive expanded all the hierarchies and named pair values and Im left with this column

But you cant expand down. I cant Close and Apply because I get errors due to this column and Im unsure what to do. I dont want to delete this column. I want to use it?

Any help would be really appreciated 

[Stachu]

are all the tables in that column in the same format? it could be PowerbI struggles with expanding if they have different scope
can you the error messages that you get when you keep this column?

[DebbieE]

I did try deleting it but this annoyingly didnt resolve the issue

We cannot convert the value “System Host Name: MI ….“ to type Table

So thats something else to try and figure out

In regards to this. Some of them are Text and some of them are null. 

Im really now sure what the best way forward is with this one

[ImkeF]

This blogpost describes how you can solve it: https://www.thebiccountant.com/2017/07/25/how-to-expand-a-column-that-cannot-be-expanded-in-power-bi...

[DebbieE]

Im sorry, Ive read the article and I still dont know how to resolve my issue 

I have lots of XML files being imported into one table

I have one field called helpdesk-ticket", type and some XML files deal with it differently to others causing the issue

For example for some files I can Expand type and see the data

For the other set of files I Expand Type and see [Table]. I then have to click on this again, choose element text and then I can see the data.

Is there any way I can stop the error so I can bring all the files in together. Ive taken two files as an example

let
Source = Folder.Files("C:\............\Exampleticketissue"),
#"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Exampleticketissue", each #"Transform File from Exampleticketissue"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Exampleticketissue"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Exampleticketissue", Table.ColumnNames(#"Transform File from Exampleticketissue"(#"Sample File (10)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"helpdesk-ticket", type any}, {"Attribute:type", type text}}),
#"Expanded helpdesk-ticket" = Table.ExpandTableColumn(#"Changed Type", "helpdesk-ticket", {"ticket-type"}, {"helpdesk-ticket.ticket-type"}),
#"Expanded helpdesk-ticket.ticket-type" = Table.ExpandTableColumn(#"Expanded helpdesk-ticket", "helpdesk-ticket.ticket-type", {"Element:Text"}, {"helpdesk-ticket.ticket-type.Element:Text"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded helpdesk-ticket.ticket-type", each true)
in
#"Filtered Rows"

[ImkeF]

If there are different structures in your tables, you either use different queries/function to open them, or you include statements like:

if Value.Is(..., ,type table) then ..do the table thing.. else .. do the text-thing..

so you basically check which type and the do the appropriate action.

It's laborious, but I cannot think of a different way.