Getting the date from the file name in PowerQuery

[IEG]

Hi,

I do have a sofware generated forecast file, however the column names are useless. I want to change a column name automatically when the data is uploaded to PowerBI. I was using below however this only works if you are using a file from this month. If I want to use a file from archive, i.e. last month's file. column headers are not correct, the first forecast month shows Mar instead of Feb.

{
"Forecast 1",
let
DateTime1 = DateTime.LocalNow(),
Year1 = Number.ToText( Date.Year( DateTime1 ) ),
Month1 = Date.MonthName( DateTime1 )

in "FG F" & Year1 & "-" & Month1
} ,

CSV files I have are named like Extract_20220330_0636.TXT, I want to get the datestamp from the file name.

Thanks

[BA_Pete]

Hi @IEG ,

Use the folder connector in Power Query instead of the Text/CSV connector.

Once you connect to the folder, you'll see all the file names in a table.

Filter the table accordingly to leave just the file that you want.

Click on the yellow nested Binary value for the file you want.

Select the [Name] column, go to Add Column tab > Extract (dropdown) > 'Text Between Delimiters'.

Put an underscore '_' in for both the start and end delimiters.

Change this column data type to Date Type.

Expand your nested table and the date you just made will disappear. The point is, we've created it and it exists in one of our step tables. To grab it later, you can create a custom step that uses the step before you expanded your nested table as a reference point. In the order that we did things here, that will be the #"Changed Types" step. So our custom step will be:

Table.Column(#"Changed Types", "Name"){0}

Now you have your full table that you wanted, and also the file date within your custom step that you can do whatever you want with just by referring to your custom step name in your code.

Pete

[IEG]

Thanks Pete, however I am lost at some point. This will give me table with my data and a column with a date in it. I want to change the header of the column according to that date, how can I use this column to do that?

[v-jingzhang · Accepted solution]

Hi @IEG 

Is this column always at the same position in different files? If so, you can change its column name based on position.

For example, there are 4 columns in below table.

Table.ColumnNames function will get back all column names in a table. It returns a list of names, so the index starts from 0. You can use Table.ColumnNames(#"previous step name"){0} to get the name of the first column. If the column you want to rename is the fifth column, you just need to change 0 to 4. 

Then when you rename a column, you can use 

= Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){4}, "new column name"}})

Based on @BA_Pete 's solution, you already get the date from file name, so you can add a custom step after that. 

= Table.RenameColumns(#"Changed Type",{{Table.ColumnNames(#"Changed Type"){4}, Table.Column(#"Changed Type", "Name"){0}}})

Renaming A Column In Power Query Based On Position — ehansalytics

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