• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

How to update folder and file names - e.g. change from 2018 in folder name to 2019|Power Query|Excel Forum|My Online Training Hub

You are here: Home / How to update folder and file names - e.g. change from 2018 in folder name to 2019|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryHow to update folder and file names…
sp_PrintTopic sp_TopicIcon
How to update folder and file names - e.g. change from 2018 in folder name to 2019
Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
1
November 19, 2018 - 11:05 pm
sp_Permalink sp_Print

hi All

I have a user who has a summary file called Summary 2018 in a folder called Pay 2018. There are 12 files feeding into this e.g. Jan 2018, Feb 2018 - all in the same folder. Is there a way in PQ to update all these to Pay 2019 and then point them at Jan 2019, Feb 2019 etc. I've tried using Edit Data but it doesn't seem to allow me to change the source> Thanks. 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 689
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
November 19, 2018 - 11:34 pm
sp_Permalink sp_Print

If they are the only files in the folder, you could simply process all the files excluding the Summary one using something like = Folder.Files("folder name here") and then filter out the Summary workbook. That way you only need to edit the folder path in the source, either editing the query, or using a single record parameter table.

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
November 20, 2018 - 8:56 am
sp_Permalink sp_Print

Hi Anne,

You should be able to change the source folder path using the Advanced Editor in Power Query Editor.
You can also change source path from Power Query Editor --> Data source settings --> Change Source...

/Anders

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
4
November 20, 2018 - 7:37 pm
sp_Permalink sp_Print

@Anders Sehlstedt and @Velouria - thank you both. So I have got as far as both the steps you have outlined. Just a couple of questions about this. I've tried updating both the file name and folder path in the query (Have loaded from folder) but when I close and load - it's not updated. I think I'm missing something obvious! Thanks. 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 880
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
5
November 21, 2018 - 3:27 am
sp_Permalink sp_Print

Hello,

I am sorry, I am not so familiar with the M language, but perhaps there can be some useful information in these pages.

https://docs.microsoft.com/en-.....-reference

But I was thinking of another approach, if it is doable in this case I do not know. But instead of having one file for each month, can it not be one file for each year and all months in respective sheet? And of course a more generic folder name. Then there would be no reason to change the path nor the files.

Any way, best of luck finding a solution.

/Anders

Avatar
Anne Walsh
Member
Members
Level 0
Forum Posts: 100
Member Since:
May 2, 2014
sp_UserOfflineSmall Offline
6
November 21, 2018 - 11:51 pm
sp_Permalink sp_Print

Well, the person has 12 files - one for each month - just the way her Payroll is set up and wants to have the same set up for 2019. Think I'm going to go back and just try the edit data feature again...thanks though 🙂

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4614
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
7
November 22, 2018 - 9:28 am
sp_Permalink sp_Print

Hi Anne,

I presume you have a new folder for the 2019 source files. In which case, make a copy of the Excel file containing the 2018 query. This will be for your 2019 data. Open the advanced editor in the new file and change the file path and any reference to 2018 with 2019. If there are a lot of references you could copy the query into Word and use Find & Replace, then copy back into the advanced editor.

You may need to refresh the query upon closing the Advanced Editor.

Mynda

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Tracy English, David Birch, QSolutions Group
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 880
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 237
Jessica Stewart: 219
A.Maurizio: 213
Aye Mu: 201
jaryszek: 183
Newest Members:
Jennifer Rodriguez-Avila
Khaled Ibrahim
Kiran Supekar
Lisa Myers
Ronald White
Ginette Guevremont
Taryn Ambrosi
Mark Davenport
Christy Nichols
Harald Endres
Forum Stats:
Groups: 3
Forums: 24
Topics: 6530
Posts: 28602

 

Member Stats:
Guest Posters: 49
Members: 32820
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.