Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ncbshiva
Advocate V
Advocate V

Dynamically changing the source from local files to Sharepoint using Parameter

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

1 ACCEPTED SOLUTION
v-danhe-msft
Employee
Employee

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:

1.PNG2.PNG

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

1.PNG

 

Regards,

Daniel He

 

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-danhe-msft
Employee
Employee

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:

1.PNG2.PNG

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

1.PNG

 

Regards,

Daniel He

 

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vinaypugalia
Resolver I
Resolver I

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.

 

 

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.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.