Source.Name Column in Transform Sample File

[Anonymous]

Hi,

I have a table called "Cost Sheets". The first column by default is called "Source.Name" and contains the name of the Excel file that the data is taken from.

This column does not appear in the "Transform Sample File" table.

Is it possible to recreate this column in the "Transform Sample File" table? This would allow me to achieve the analysis I am looking for.

Many thanks, Adam

[Syndicate_Admin]

Hi @adam-starkie ,

When connecting to a folder to combine files, Transform Sample File table has been quoted the sample file name already in Power Query.

If not want to change these initial queries, the direct way to achieve it is to duplicate the Sample file query as a new query without the file name, then add a custom column like this in Transform Sample File

= Table.AddColumn(#"Promoted Headers", "File Name", each #"Sample File (2)"{0}[Name])

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Hi @adam-starkie ,

When connecting to a folder to combine files, Transform Sample File table has been quoted the sample file name already in Power Query.

If not want to change these initial queries, the direct way to achieve it is to duplicate the Sample file query as a new query without the file name, then add a custom column like this in Transform Sample File

= Table.AddColumn(#"Promoted Headers", "File Name", each #"Sample File (2)"{0}[Name])

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yingjl]

Hi @Anonymous ,

When connecting to a folder to combine files, Transform Sample File table has been quoted the sample file name already in Power Query.

If not want to change these initial queries, the direct way to achieve it is to duplicate the Sample file query as a new query without the file name, then add a custom column like this in Transform Sample File

= Table.AddColumn(#"Promoted Headers", "File Name", each #"Sample File (2)"{0}[Name])

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

So if you are using a transform file, I would assume that you have the source as a folder. Can't you just keep the Column with the name of the file, rename it Source.Name, and then after applying the transforms in the sample file, expanding the tables?

--Nate

[Anonymous]

Hi Nate,

Thank you for the response. Yes, the source is a folder and the files within that folder have identical structure. Key information is stored in the filenames of the individual files.

When I combine and transform the files from the folder, I can see that there is a column called Source.Name containing the filenames by default - however when I go into the 'Transform Sample File' tab to apply my transformations, there is no filename column. 

How do I recreate this column? I feel like I'm missing something really obvious here.

Thansk, Adam

[Ehren]

As you've observed, by default the "Transform Sample File" query doesn't have access to the file name. One way to change this would be to add a file-level query parameter called FileName and reference this in "Transform Sample File". This will add a FileName parameter to the function that's auto-generated from "Transform Sample File". Then you just need to update the place where the generated function is called to pass in the actual file name as well as the other info.

Another option would be to utilize the file name after the custom function is called. But perhaps that's too late for what you're trying to do.