Dynamically changing the source from local files to Sharepoint using Parameter

[ncbshiva]

Hi Team,

Is it possible to create a parameter in Power BI , that swichtes the source between the local files and the same files stored in the sharepoint. If possible please let me know how to create this ?

Regards

[v-danhe-msft · Accepted solution]

Hi @ncbshiva,

Based on my test, you could refer to below steps in query editor:

Open the Advanced language and enter below code:

(para as number) =>
if para=1
then
let
    Source = Excel.Workbook(Web.Contents("your sharepoint address"), null, true),
    Talk_Sheet = Source{[Item="Talk",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Talk_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Full date", type date}, {"sale", Int64.Type}})
in
    #"Changed Type"
else 
let
    Source = Excel.Workbook(File.Contents("your local file address"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Full date", type date}, {"sale", Int64.Type}})
in
   #"Changed Type"

If you enter the number 1, it will load the data in your sharepoint:

If you enter the number 2, it will load the data in your local file:

Regards,

Daniel He

[vinaypugalia]

Hello,

AFAIK there is no way to switch the datasource dynamically.

However, if you can share some more details on WHAT you want to do instead of HOW you want to it, possibly we can share some ideas.

[ncbshiva]

I have a requirement to connect to sharepoint files and sql server. To connect to sql server i need to use the VPN connection.

If i use that my internet will not work and i will not be albe to connect to sharepoint files. Because i need to merge both sharepoint files and data from the sql server after some transformations.

[vinaypugalia]

In this case, what I understand is, you will have to develop logic to timely pull the data from SQL & add that to Sharepoint. You can achieve this by writing some custom code/scripts or use MS Flow or alike. Once all your data is in SharePoint, you can then design your Power BI reports accordingly.

Hope, this helps!