Change from C drive to SharePoint folder

[GavinR87]

Hi all,

I have made a Power BI report containing a lot of data that is saved in a SharePoint site folder.

What I have done is synced the SharePoint site to my OneDrive, and used the folder option in Power BI to load all the files contained in a certain folder.

Unfortunately, being new to this, I now realise that all this data is linked to C:/users/work/FOLDER not https://COMPANY.sharepoint.com/sites/SITE_NAME/Shared Documents/General/Power BI/FOLDER/.

I had assumed when I had clicked folder as it was synced to SharePoint it was an "online" folder not a C drive.

Now my question is how can I change the data source from being a folder on my C drive to a folder in the SharePoint? I have tried many things, such as changing the path of the data source to the folder URL. Also I need the source changing but all the transformations that I have done to the data retained.

I also tried adding a new data source using the "SharePoint folder" option. Now for some reason this only lets you put the root directory of the SharePoint site and pulls in absolutely everything file contained in the SharePoint site, and I can't seem to select just the folder I want.

Grateful for any adivce here 🙂

[Anupa_Jayakody]

Hi, I created a power bi report using a One drive folder as the data source, but now i want to change it to a sharepoint folder. 

When i tried changing it in "source", it doesnt read the sharepoint folder path I copy paste and generates this message, 

"This folder path must be a valid absolute path". 

Can you advise how I can resolve this??

[Anupa_Jayakody]

Hi, I created a power bi report using a One drive folder as the data source, but now i want to change it to a sharepoint folder. 

When i tried changing it in "source", it doesnt read the sharepoint folder path I copy paste and generates this message, 

"This folder path must be a valid absolute path". 

Can you advise how I can resolve this??

[GavinR87]

Hi all,

Thanks so much for taking the time to replies.

I've got some time set aside later this week to try these solutions - hopefully will be able to get one to work!

Thanks again 🙂

[v-shex-msft]

Hi @GavinR87 ,

Please take a look at the following document to know how to extract the root file path of SharePoint/onedrive files, then you can use this to replace the local folder path:
Use OneDrive for Business links in Power BI Desktop 

Regards,

Xiaoxin Sheng

[mwimberger]

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 

[GavinR87]

Hi @mwimberger 

Thanks so much for the detailed reply!

I think I am almost there but getting a bit stuck.

I have got to step 2 and I am at the following point (see screenshot below). I am stuck at what to do next.

I have filter to the folder I want, but it doesn't seem to load the data in from all my CSV files.

I am still very new to Power BI, so don't know exactly where to paste the code into that you have kindly provided me. Could you give me any more pointers if possible?

Cheers

[mwimberger · Accepted solution]

Hi @GavinR87 

The next step will load up all your CSV's together 

- In the Power Query Editor, on the "Add Column" tab choose " Custom Column"

Next step is to remove all the other columns except the custom one you just created, by right clicking on the custom column and choose remove others:

Then you need to expand the custom column by clicking the expand button on the top right of the column :

And then the ( possibly ) final step is to promote headers by clickling on Transform: Use first row as headers:

Now you will have the column headings that are in your CSV files as headings in your table and you can do all the other transforms you need.

PS: When I  mentioned copying the code over - it is using the Advanced Editor - that you may not have used before. As you become more versed in Power Query you will start relying on the Advanced Editor to copy out code and reuse in other situtations as you can then tweak it and adjust to your needs: This is where it is located in Power Query - there are two ways to get to it:

You will now see the M code that has been translated from all the mouse clicks you performed:

I hope this provides more clarity on the steps I provided in the first answer. Sometime screen shots is a better way to guide one through the process. 

Also Ruth from  Curbal has a great video on this on Youtube:

A cleaner, better and faster way to bulk import files in Power BI 

Have a great weekend!

Cheers

Manfred

[parry2k]

@GavinR87 you can surely do that, you can use sharepoint folder in your report, you need to change data source for your table to sharepoint folder. Hope this link helps.