Creating Robust Sources of Data for Queries

[Mischief63]

Hi All. I'm setting up our financial reporting system. We'll be querying out of a Oracle Peoplesoft Database and saving files on a network drive. My plan was to have the data be placed in those folders monthly and then write the Query to retrieve data from those folders.

As I discovered today, if any files are moved or renamed, the query is toast.

Now it makes sense that the data isn't where it was original located. But people move files and rename things all the time by mistake. It looks one moved folder could paralyze the entire reporting system. You can't seem to just move it back and pick up where you left off.

This must be a common concern, how do people deal with it?

Thanks!

 

[Matt Allington]

If you rename your oracle database or server name the same thing will happen of course. The issue is that there is rarely discipline on files but there is on servers. I suggest you use import from folder, as then the file name is irrelevant. It still has to be there though. I wrote a series of blog articles a out this here Combine Excel Workbooks with Power Query – Method 1 - Excelerator BI

i also have an online training course if you are interested. Power Query for Excel and Power BI Online Training - Excelerator BI