cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adam-starkie
Frequent Visitor

Source.Name Column in Transform Sample File

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

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

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])

vyingjl_0-1635145502143.pngvyingjl_1-1635145520145.png

 

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
Administrator
Administrator

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])

vyingjl_0-1635145502143.pngvyingjl_1-1635145520145.png

 

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
Community Support
Community Support

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])

vyingjl_0-1635145502143.pngvyingjl_1-1635145520145.png

 

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
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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
Microsoft
Microsoft

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.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors