adding the file name to a column in power query

[Anonymous]

Hello Community  -  I have a file that is in a corporate one drive folder.   Below. 

The file is called December 2020.   

My understanding is that you can get Power Query to automatically add the file name to a column in the table.  In this case, I would want to add a column that contains "December 2020".   

I actually have a year's worth of files in this folder, and I know I can also use the invoke function command to bring them in all at once.   But just not specifically sure how to extract the file name, and add it to the column.    Each of the files is named similarly:  January 2021   February 2021  etc  so they should all be able to be changed to a date data type. 

https://xxxxxxx-my.sharepoint.com/personal/xxxxxxxxxx/Documents/Historical%20CRM%20Locked%20Forecast...

[comish4lif2]

This is great if your files are in SharePoint, or if you are doing a folder import. 
But what if you are importing a single txt file and want to grab the file name for the purpose of extracting the date?

[v-jingzhang]

Hi @comish4lif2 

For a single file, you can also use the SharePoint folder or Folder connector, filter by [Name] column to leave only that file row in the query then continue from Step 4 of the same solution. 

Best regards,

Jing

[comish4lif2]

So the only way is thru a folder import?

There's no easy way to add a new column in power query and populate the column with the name of the file?

[rahuldas_vgm]

I had a similar problem n it worked for me. Thank You.

[v-jingzhang]

Hi @Anonymous 

Have you solved this problem? Kindly accept a proper reply as solution, or share your own solution if you have got some better ones. This would sure help more people who may have similar requirements. Thanks. 

Best Regards,
Community Support Team _ Jing

[v-jingzhang · Accepted solution]

Hi @Anonymous 

You could use Sharepoint folder connector to connect to your OneDrive for business site and use combine files feature after getting a file list in the folder you want to connect to. But you need to edit or add some steps during the process. I will show you how to do that. 

Steps:

1. Go to Get Data > File > SharePoint folder > Connect. Enter https://xxxxx-my.sharepoint.com/personal/xxxxxxxxcom into Site URL textbox.

Note: Only enter the part before _layouts in the whole URL (https://xxxxx-my.sharepoint.com/personal/xxxxxxxxxxxxx_com/_layouts/15/onedrive.aspx?id=%2Fpersonal%2Fxxxxxxxxxxxxxxx%2FDocuments%2FTestFolder%2FData).

2. Once it connects and shows a full file list, click on Transform Data button. Then you will go to Source step in Power Query editor. 

3. Filter rows on Folder Path column and only select the folder path you want to import data from. This will keep only files from that folder. 

4. Remove other columns except for Content column and Name column. Combine files in Content column.

 

5. After a while, you will get a combined table called Query1. However this table doesn't have file name in it. In this query, you may see below steps. Remove several bottom steps untill you see Name column.

6. Remove Content column. Expand Transform File column. Then you will have data from all files into a table. The Name column also exists. 

Result

Hope this helps.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Peglaws]

This (and other similar solutions) work fine for me EXCEPT --- if the top file within the folder (the one that will appear at the top of the query) is removed from the folder the query breaks, producing this error - [Expression.Error] The column 'first file name' of the table wasn't found.  Adding or removing any other file to or from that folder works perfectly. If that top file is copied back into the folder, the query is fixed.

I've been trying everything I can think of to fix this, but no luck. Any thoughts, anyone?

[parry2k]

@Anonymous connect using one drive for business using SharePoint files or contents, it will list all the files and there you will get file name column and then you can call a function or combine files together, and keep the file name as the part of the data.

Read more here 

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Vera_33]

Hi @Anonymous 

You need to provide some sample data, not sure your files are exactly named like January 2021   February 2021, or contains? If they are like Jan 2021, Feb 2021, then you can add a custom column

Date.From( "1 " & Text.BeforeDelimiter([Name],"."))

[Anonymous]

@Vera_33    My files are named per what I mentioned in my post.   January 2021   February 2021 etc  (and as per the example path given.   

My question is how to create the column, from the file name, upon import of the file.