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

     

     

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!