Add column to nested tables using outer value in Power Query

Stefano Verugi 0
Viewed 39

I have an outer table with "Name" and "Content" columns, I also have nested tables contained in the "Content" column

enter image description here

How do I add a new column in the nested tables using the value "Name" from the outer one?

enter image description here

If I add a new column in the outer using

= Table.AddColumn(Step-1,"NewColOut", each Table.AddColumn([Content],"FileName", (x)=> [Name]))

I have no problem, what if I want to transform "Content" without adding a new column in the outer one?

I tried Table.TransformColumns but to no avail, I am not able to bring in the "Name" value at the nested table level

any help would be greately appreciated

powerquery
1 Answers

You don't really need to do this, since if you expand the embedded table, it will automatically copy down the filename, but if you wanted to, you could use the simple line

#"Added Custom1" = Table.AddColumn(#"PriorStepNameGoesHere, "NewColOut", each let name=[Name] in  Table.AddColumn([Content],"Filename",each name))

or with transform

#"Added Custom1"= Table.FromRecords(Table.TransformRows(#"PriorStepNameGoesHere",
     (r) => Record.TransformFields(r,
     {"Content", each Table.AddColumn(_,"NewColOut",each r[Name]) })))
horseyride0
Related