Change from C drive to SharePoint folder

Hi GavinR87 

The change between physical folders and Sharepoint takes some getting used to, especially the way that folders work.

Here are the steps I would follow:

1) copy your M code out of the advanced editor in Power Query and paste into a text editor like Notepad ( or Notepad++ which you can stylise to show M markup

2) Create a new query directing to the Sharepoint folder: 

Note you will have to use filters to get to the folder you want

Also, you mention that you have a lot of data - which I presume is that you may have a lot of files within the folder? For that I have a suggestion to prevent the creation of custom functions and the helper queries that are created 

OK, Here is the code ( with what you posted in as your example dummy folders / path; and I will step you though what each step does below:

let
  Source = SharePoint.Files("https://COMPANY.sharepoint.com/sites/SITE_NAME", [ApiVersion = 15]),
    Filtered_Rows = Table.SelectRows(Source, each Text.Contains([Folder Path], "PowerBI/FOLDER")),
    #"Filtered Hidden Files1" = Table.SelectRows(Filtered_Rows, each [Attributes]?[Hidden]? <> true),
    #"Added Custom4" = Table.AddColumn(#"Filtered Hidden Files1", "Custom", each Csv.Document([Content])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom4", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23"}, {"Custom.Column1", "Custom.Column2", "Custom.Column3", "Custom.Column4", "Custom.Column5", "Custom.Column6", "Custom.Column7", "Custom.Column8", "Custom.Column9", "Custom.Column10", "Custom.Column11", "Custom.Column12", "Custom.Column13", "Custom.Column14", "Custom.Column15", "Custom.Column16", "Custom.Column17", "Custom.Column18", "Custom.Column19", "Custom.Column20", "Custom.Column21", "Custom.Column22", "Custom.Column23"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns1", [PromoteAllScalars=true]),

Step: Filtered Rows allows you to drill down to the proper folder - I use Text.Contains to get the appropriate folder, or you can use an explicit folder.

Step: Added Custom4 adds a custom column to extract  the CSVs from the folder, and list them as a TABLE which needs to be expanded ( next step)

I then remove all the unneccesary columns except for the columns in the expanded Custom section, and then promote the headers.

This should have you with all your files joined in one space and you can then append the rest of your M code in (from the line where the next steps of your ETL continue from, remembering to have the first line of your appended code to reference #"Promoted Headers" and remember to end your preceding line of M with a comma so that you don't get any errors.

If you need to join Excel Files instead of CSV files, use this Excel.Workbook instead of Csv.Document in the Custom4 step

I had the same issue as you when I went from physical drive letter, to a Sharepoint folder. Once you get your head around it , it is pretty Straight forward. Also I have added another step to this to avoid the auto generation of Helper queries / Custom Functions - and you may not need this in your situation. 

If you have any further questions, please reach out.

Cheers

Manfred