Question about how a Source is identified in Power Query

[phantomias33]

I currently have a dashboard that utilizes 5 different Excel sheets, all share a common root folder (some nested in subfolders).  As of now, the report works great and is published on the corporate enterprise network.  IT set up an SVC.PowerBI account with rights to the common root folder of all the sources. My issue is that apparently in order to setup a scheduled refresh of the semantic model on the workspace (and save IT from setting up individual connections to each source), I need to modify my source settings within Power Query from:

Source = Excel.Workbook(File.Contents("\\network path to folder"), nuyll, true),

to:

Source = Folder.Files("\\network path to folder")

I've started modifying the "Source =" setting by copying and pasting the "Source = Folder.Files" pathway when selecting Source in the Applied Steps section of Power Query.  I then take the following steps within Power Query:

• Identify the file for that particular query
• Click on the 'Combine Files' icon
• When asked if I want to insert a step, I confirm yes
• Identify the relevant sheet within that Excel file
• Click Ok

I then go through the process of troubleshooting any subsequent errors in the applied steps, including newly formed duplicated steps.  

However, this process produces a plethora of 'Helper Queries' and I have to think I'm going about this the wrong way.  I'm not sure if I'm making any sense but I guess the essence of my question is:

How do I change a currently functioning dashboard from using:

Source = Excel.Workbook(File.Contents("\\network path to folder"), nuyll, true),

to:

Source = Folder.Files("\\network path to folder")

Any help or pointers would be greatly appreciated.

[lbendlin · Accepted solution]

Click on the 'Combine Files' icon

Yeah, don't do that.  That feature is for beginners.  What you can do is study how it works, then delete that group and create your own process.

1. Connect to folder

2. Filter for the files you want

3. pick one file (any file) and transform it into the raw table

4. Inspect the M code and convert it into a function

5. Delete the steps from 3.

6. Add a custom column that calls the function you created in 4.

7. Expand the table column

8. Apply the rest of the transforms (like changing column types)

[phantomias33]

lbendlin-

Thank you!  I'm not sure I've graduated past beginner status but you've definitely given me some new avenues to chase.  (New-ish to DAX but not coding so I'll dive in)