I've recently figured out how to pass a single column from a parent table to nested tables by creating a custom column and using the following formula:
Table.AddColumn([FX_HR_SV_File_Initial_Transform], "Course_Name", (r)=> [Course_Name])
However, I need to pass a further 5 columns from the parent table into each nested table. Is there a way of doing this in one step? without having to add another 5 steps that each reference the table created in the step before?
Many Thanks
Hi Richard,
I'm not sure if you are appending or merging data, it'll depend on the structure of our tables. But you could try something like this
Table.Combine({[Custom], Table.SelectColumns( Source, {"Col1", "Col2", "Col3", "Col4", "Col5"}) })
If that doesn't solve it, please supply a file with some dummy data.
Regards
Phil
Hi Philip
Thanks for your suggestion
I have the same question as Richard K as I have used the same solution for a long time .
My query (FileInfo) has four columns
Col1 name= File name
Col2 name= Date Modified
Col3 name = File path
Col4 name = Custom (nested/ Grouped tables
I want to add multiples columns from the Outer table (FileInfo) to the nested Table (Custom)
Your solution only combines the Nested table(FileInfo) with Selected columns from the previous step,
It should
Source = Folder.Files("<YOUR FOLDER>"),
BinaryBuffer = Table.TransformColumns(Source,{{"Content", Binary.Buffer}})
TransFormBinary = Table.TransformColumns(BinaryBuffer,{{"Content", each Excel.Workbook(_,true)}}),
Rename = Table.RenameColumns(TransFormBinary,{{"Name", "File name"}})
AddColToNestedtbl = Table.TransformColumns(Rename", {"Content", (i)=> Table.AddColumn(i, "FileInfoTable", (R)=> Table.Combine({ i, Table.SelectColumns(Rename, {"File name", "Date modified"}) }))})
Hi Jan,
I'm not sure if you are asking me something?
regards
Phil
