How to update an Access database file on SharePoint via Power Query M language

[Anonymous]

Dears,

I'm designing an internal reporting application.

The data is in excel spreadsheet (every week will have a new one).

I know I can simply create a floder and just put new added excel files in and then Power BI will refresh the data.

The excel file contains quite a lot data rows, and it supposes to keep 2 years' data. My understanding is that every time when do data refreshing, Power BI will process all the files in the folder, so I'm just worring about the efficiency, it may take too long. (Please correct me if my understanding is wrong 😊 )

So here is my plan:

1) Set an Access database file, and put it in SharePoint

2) Everytime when there is new data, just put the excel data file in the folder, then Power BI will read new data in

3) Insert new data into the Access datafile.

I have figured out the way to insert data by using Value.NativeQuery().

Yet, I got problem to connect Access database file via Access.Database('https://^^^^^^.sharepoint.com/teams/^^^^^^^^/Shared%20Documents/TestAccessPowerBI/MutipleTables.accdb' ),

then I got the error message:

We cannot convert the value "https://^^^^^.s..." to type Binary

Seems that I gave the wrong parameter.

How could I fix it? 

Thanks a lot.

[Anonymous · Accepted solution]

Just add File.Contents() is enough.  

I tried to connect the file via Power BI's native function and then find the code in step "Source" like this:

Access.Database(File.Contents("\\^^^^^.sharepoint.com@SSL\DavWWWRoot\teams\^^^^^\Shared Documents\TestAccessPowerBI\MutipleTables.accdb"), [CreateNavigationProperties=true])

How stupid I am😥

[v-eachen-msft]

Hi @Anonymous ,

I am glad that you have found the solution, you could accept your own reply to close this topic.