XML Query Column which contains both "Table" and "Records"

[DR1]

The Column "SINGLE_ROOM_AREA" contains "Table" and values. But the tables only contain one value. How do I get this values expanded?

[MarcelBeug · Accepted solution]

You can transform the column like in the second step of this query:

let
    Source = #table({"SINGLE_ROOM_AREA"},{{"150"},{#table(type table[#"Element:text" = text],{{"51"}})}}),
    Transformed = Table.TransformColumns(Source, {{"SINGLE_ROOM_AREA", each if _ is table then Table.FirstValue(_) else _}})
in
    Transformed

[z22741]

@MarcelBeug 

What if I need to get all the data not just the first one?

Thanks!

[Anonymous]

Saved my day!

[DR1]

Thank you @MarcelBeug