Combine multiple nested tables from XLM datasource

[jagostinhoCT]

Hello,

I want to combine multiple nested tables from an XLM datasource.

Can I do this from the UI?

I am not sure that the correct term is "combine". The goal is to get the info one gets when clicking on the Table link, but for all tables at the same time.

Thank you

J

[v-shex-msft]

Hi @jagostinhoCT,

I think Table.Combine function will suitable for your requirement. 

You can use 'query name'['Column Name'] to get specific column contents as list, then use this as parameter to in above function.

Sample:

Custom= Table.Combine(#'Query name'['Column Name'])

Reference:

Table.Combine

Operators

Regards,

Xiaoxin Sheng

[jagostinhoCT]

Thank you @v-shex-msft.

I was able to add a new column using the Table.Combin function but I am getting an error. And the Advanced Editor is showing anything but the function I used.

See the last lines of the Advanced Editor below, if it helps to understand what I am not able to make it work.

The function I added was

Table.Combine([clashobjects.clashobject.pathlink.node])

Thank you for your help.

J

----------------------------

clashresult = clashresults{0}[clashresult],
#"Changed Type3" = Table.TransformColumnTypes(clashresult,{{"gridlocation", type text}, {"Attribute:name", type text}, {"Attribute:guid", type text}, {"Attribute:distance", type number}}),
#"Expanded clashobjects" = Table.ExpandTableColumn(#"Changed Type3", "clashobjects", {"clashobject"}, {"clashobjects.clashobject"}),
#"Expanded clashobjects.clashobject" = Table.ExpandTableColumn(#"Expanded clashobjects", "clashobjects.clashobject", {"objectattribute", "layer", "pathlink"}, {"clashobjects.clashobject.objectattribute", "clashobjects.clashobject.layer", "clashobjects.clashobject.pathlink"}),
#"Expanded clashobjects.clashobject.pathlink" = Table.ExpandTableColumn(#"Expanded clashobjects.clashobject", "clashobjects.clashobject.pathlink", {"node"}, {"clashobjects.clashobject.pathlink.node"})
in
#"Expanded clashobjects.clashobject.pathlink"

[v-shex-msft · Accepted solution]

Hi @jagostinhoCT,

Nope, this function only works for list of table, when you use it in add custom column function(it contains 'each' Iterator), [Column Name] only means one item of list.

Please use #'previous steps'['column name'] to instead.

Sample:

Custom Column = Table.AddColumn(#"Added Custom", "Custom.1", each Table.Combine(#"Expanded clashobjects.clashobject.pathlink"[clashobjects.clashobject.pathlink.node]))

Regards,

Xiaoxin Sheng