The use case.
I have a PBIX file that contains a SQL query which has a "WHERE" clause that is parameterized. The parameter is derived from an excel workbook that user updates.
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-use-onedrive-business-links
I am connecting to excel file on one drive as Web Source. The code works fine locally but I am unable to schedule refresh. I know it is not an issue with connecting to excel as Web source as I have done this previously for other dataset.
Please help as the user ends up opening excel file and refreshing and then opens the PBIX file to refresh.
Here is the code
let
Source = Excel.Workbook(Web.Contents("https://xxxxxxxxxxxxx/sites/yyyyyyyyyyyyy/Shared%20Documents/Configuration/BTech%20Business%20Plan%20Data.xlsx"), null, true),
#"JobLIst(Don't Update)_Sheet" = Source{[Item="JobLIst(Don't Update)",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"JobLIst(Don't Update)_Sheet", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job Code", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each "'"&[Job Code]&"'"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Job Code"}),
Custom = #"Removed Columns"[Custom],
Custom1 = Text.Combine(Custom,","),
Source1 =Oracle.Database("oracledbprod/prod", [HierarchicalNavigation=true]),
SQL_Code = "SELECT *
FROM da.zzzzz
WHERE JCDT_JOB_CODE IN (" & Custom1 &")",
Output = Value.NativeQuery( Source1, SQL_Code)
in
Output
