Forum Discussion

ncbshiva's avatar
ncbshiva
Icon for Advocate V rankAdvocate V
7 years ago
Solved

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 cre...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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's avatar
vinaypugalia
Icon for Resolver I rankResolver I
7 years ago

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's avatar
    ncbshiva
    Icon for Advocate V rankAdvocate V
    7 years ago

    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's avatar
      vinaypugalia
      Icon for Resolver I rankResolver I
      7 years ago

      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!