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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.