XML Query has Values and Nested Tables. How do I retrieve the first value of the table?

[Vosyn]

Just for some background - I am completely new to Power Query. I downloaded all public XMLs from clinicaltrials.gov and would have sorted the files I needed. I run a Power Query on the folder (which contains approximately 40,000 XML files). Everything loads as expected except for two columns: start_date & completion_date as pictured. Both of these columns contain dates within individual cells as well as nested tables. If I click on a table, it shows additional cells. I really only need the date that is in the first column of this nested table. The code I have so far to is below. Is there a way I could display the dates in these columns as well as the date from first value of the nested tables in the columns?

Interestingly, Power Query allows me to expand the primary_completion_date column without any issue (as shown). If I use the same function to expand start_date or completion_date columns - I get an error "Expression.Error: We cannot convert the value "September 1997" to type Table." (as pictured).

let

     Source = Folder.Files("C:\Users\Vosyn\Documents\NIH"),
     #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
     #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
     #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
     #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
     #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
     #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"required_header", type any}, {"id_info", type any}, {"brief_title", type text}, {"acronym", type text}, {"official_title", type text}, {"sponsors", type any}, {"source", type text}, {"oversight_info", type any}, {"brief_summary", type any}, {"detailed_description", type any}, {"overall_status", type text}, {"start_date", type any}, {"completion_date", type any}, {"primary_completion_date", type any}, {"phase", type text}, {"study_type", type text}, {"has_expanded_access", type text}, {"study_design_info", type any}, {"primary_outcome", type any}, {"number_of_arms", Int64.Type}, {"enrollment", type any}, {"condition", type any}, {"arm_group", type any}, {"intervention", type any}, {"eligibility", type any}, {"overall_official", type any}, {"reference", type any}, {"verification_date", type date}, {"study_first_submitted", type date}, {"study_first_submitted_qc", type date}, {"study_first_posted", type any}, {"results_first_submitted", type date}, {"results_first_submitted_qc", type date}, {"results_first_posted", type any}, {"last_update_submitted", type date}, {"last_update_submitted_qc", type date}, {"last_update_posted", type any}, {"responsible_party", type any}, {"condition_browse", type any}, {"intervention_browse", type any}, {"provided_document_section", type any}, {"clinical_results", type any}})


in

#"Changed Type"

[v-xulin-mstf]

Hi @Vosyn,

Is your issue solved?

If the issue has been solved, please adopt the solution to help others.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[Vosyn]

I have been using conditional columns with the function "=Table.FirstValue([start_date] as table)." I get close to what I need. However, the issue I have now, is that if the cell already contains a date, this function will just return Error into the cell.

[v-xulin-mstf]

Hi @Vosyn,

Try to modify the code.

#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File","Sample File")),

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!