Forum Discussion
Getting the date from the file name in PowerQuery
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.
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
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?
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.