Variable data in a column

[Gilgam]

Hello,

I have an Excel file linked to my .xml. files. The files is generated by an external system two or three times a day with a .cmd file and opens the Excel report automatically. Some columns have table data which I expand. My issue is that I have two columns that have numeric or text data in the morning and table data later in the day. If I don't expand it I get [table] results at some point, and If I do expand the columns I received an error message. I have tried with a few if statement but I'm new with M and could not manage anything. Is there a way to dectec the data type and expand if necessary ? 

Many thanks !

[PhilipTreacy · Accepted solution]

Hi @Gilgam 

Download example XLSX file

Yes you can check the data type like this

if [Files] is table then "Table" else if [Files] is list then "List" else if [Files] is record then "Record" else null

In your case you can check for a table and then expand it like this

if [Files] is table then Table.ExpandTableColumn() "Table" else [Files]

I can't fill in the arguments for the Table.ExpandTableColumn function as I don't know the structure of your data, you will need to do that.  Or post back here with sample data so I can help with it.

Regards

Phil

[Gilgam]

Thank you very much ! I managed to do it with your post and Excel file !