Schedule Refresh Not Working - Dynamic Data Source

[azakir]

Hi Guys. 

I have a report that uses a number of tables. It refreshes fine on Power BI Desktop, but on service, I can't schedule refresh and get the following error: 

I looked into my tables and found one of them that uses a Dyamic data source. M Query below: 

let
// Function to get data from a database and add the DBName column
GetDataFromDB = (DBName as text) as table =>
let
Source = Sql.Database("PSGSQL05", DBName, [
Query="select a.*#(lf)#(tab),case when a.CollarError > CollarErrorTolerance then 1 else 2 end as [SpatialAccuracy(XY)]#(lf)#(tab),IIF(a.CollarZError > CollarZToleranceLowerBound and a.CollarZError < CollarZToleranceUpperBound,2,1) as [SpatialAccuracy(Z)]#(lf)--#(tab),IIF(a.DrillDepthError > DrillDepthErrorToleranceLowerBound and a.DrillDepthError < DrillDepthErrorToleranceUpperBound,2,1) as [DepthCompliance]#(lf)#(tab),IIF(a.DrillDepthError > DrillDepthErrorToleranceLowerBound and a.DrillDepthError < DrillDepthErrorToleranceUpperBound,2,#(lf)#(tab)#(tab)IIF(a.DrillDepthError < DrillDepthErrorToleranceLowerBound,3, #(tab)#(lf)#(tab)1)) as [DepthCompliance]#(lf)#(tab),format(DrillingCompletedTime,'dd/MM/yyyy') as DrillingCompletedDate#(lf)--#(tab),a.ActualDepth#(lf)--#(tab),c.LastDipDepth#(lf)--#(tab),a.TargetDrillDepth#(lf)#(tab),IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) as [Depth]#(lf)#(tab),(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth)) - a.TargetDrillDepth as [Length]#(lf)#(tab),IIF( ((IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth)) - c.TargetChargeDepth) > DrillDepthErrorToleranceUpperBound,'Long',#(lf)#(tab)#(tab)IIF( ((IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth)) - c.TargetChargeDepth) < DrillDepthErrorToleranceLowerBound,'Short','WithinTolerance')) #(lf)#(tab)as [DrillCompliance]#(lf)#(tab)--,IIF (#(lf)#(tab)--#(tab)(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth) <= 1,' <1m',#(lf)#(tab)--#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=3,' 1-3m',#(lf)#(tab)--#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <= 5,' 3-5m','>5m'#(lf)#(tab)--#(tab)#(tab)#(tab)))) as [LengthGrouping]#(lf)#(tab),IIF (#(lf)#(tab)#(tab)(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth) < -1,'<-1m',#(lf)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=0,'-1-0m',#(lf)#(tab)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=1,'0-1m',#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=3,'1-3m',#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <= 5,'3-5m','>5m'#(lf)#(tab)#(tab)#(tab)#(tab)))))) as [LengthGrouping]#(lf)#(tab)#(tab),IIF (#(lf)#(tab)#(tab)(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth) < -1,1,#(lf)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=0,2,#(lf)#(tab)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=1,3,#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <=3,4,#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)IIF(IIF(c.LastDipDepth is null, a.actualdepth,c.LastDipDepth) - a.TargetDrillDepth <= 5,5,6#(lf)#(tab)#(tab)#(tab)#(tab)))))) as [LengthGroupingSort]#(lf)#(tab)#(tab)#(lf)from DW.DrillingEntry a#(lf)#(tab)left join DW.SiteParameter b#(lf)#(tab)#(tab)on 1 = b.Id#(lf)#(tab)left join DW.Hole c#(lf)#(tab)#(tab)on a.HoleId=c.Id"]),
AddDBName = Table.AddColumn(Source, "DBName", each DBName)
in
AddDBName,

// Fetch data from all databases
Data_ODC = GetDataFromDB("BlastLogic_PRODUCTION_703_Site_ODC"),
Data_MAS = GetDataFromDB("BlastLogic_PRODUCTION_703_Site_MAS"),
Data_MPO = GetDataFromDB("BlastLogic_PRODUCTION_703_Site_MPO"),
Data_S3 = GetDataFromDB("BlastLogic_PRODUCTION_703_Site_S3"),

// Combine all data
CombinedData = Table.Combine({Data_ODC, Data_MAS, Data_MPO, Data_S3})
in
CombinedData

Could someone help me make this from dynamic data source to static please. 

[v-jtian-msft]

Hi,@azakir .I am glad to help you.
Although I am not very good at data conversion, I would like to make the following suggestion to you
I noticed that your dynamic data source is using a SQL Server database.
I recommend the following suggestions:
1. Use parameterized queries: create parameterized queries in Power BI Desktop and then use these parameters in Power BI Services.
2. You can use your data source to get M code to create an identical dataflow, because it is a SQL Server type of data source, I think it should be possible to achieve through dataflow
I also found articles about dynamic data source conversion, hope this helps:
URL:

Solved: Power BI Service with dynamic data sources - Microsoft Fabric Community
Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...
Chris Webb's BI Blog: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI (crossjoin....

Load data from a dynamic data source into a Power BI data stream and then use the data stream as the data source in a report. Data Stream Support for Scheduled Refresh

You can create a dataflow in the power BI service/datafactory using the gen1/gen2 dataflow

like this:

Paste your M code into the blank query

Choose the right data destination

Setting up incremental refresh for dataflow
Configuring the dataflow in Power BI service is similar:

Configure a refresh schedule for dataflow to use the newly created dataflow as a data source

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[azakir · Accepted solution]

Thanks for the reply @v-jtian-msft. I did try the following to change from dynamic to static. Although I do like your solution better: 

Data_ODC = Sql.Database("PSGSQL05", "BlastLogic_PRODUCTION_703_Site_ODC", [Query="
SELECT 'BlastLogic_PRODUCTION_703_Site_ODC' AS DatabaseName, a.*,
CASE WHEN a.CollarError > CollarErrorTolerance THEN 1 ELSE 2 END AS [SpatialAccuracy(XY)],
CASE WHEN a.CollarZError > CollarZToleranceLowerBound AND a.CollarZError < CollarZToleranceUpperBound THEN 2 ELSE 1 END AS [SpatialAccuracy(Z)],
CASE WHEN a.DrillDepthError > DrillDepthErrorToleranceLowerBound AND a.DrillDepthError < DrillDepthErrorToleranceUpperBound THEN 2 ELSE 1 END AS [DepthCompliance],
CASE WHEN a.DrillDepthError > DrillDepthErrorToleranceLowerBound AND a.DrillDepthError < DrillDepthErrorToleranceUpperBound THEN 2
WHEN a.DrillDepthError < DrillDepthErrorToleranceLowerBound THEN 3 ELSE 1 END AS [DepthCompliance2],
FORMAT(a.DrillingCompletedTime, 'dd/MM/yyyy') AS DrillingCompletedDate,
a.ActualDepth,
c.LastDipDepth,
a.TargetDrillDepth,
CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END AS [Depth],
(CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth AS [Length],
CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - c.TargetChargeDepth > DrillDepthErrorToleranceUpperBound THEN 'Long'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - c.TargetChargeDepth < DrillDepthErrorToleranceLowerBound THEN 'Short'
ELSE 'WithinTolerance' END AS [DrillCompliance],
CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN ' <1m'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN ' 1-3m'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN ' 3-5m'
ELSE '>5m' END AS [LengthGrouping],
CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth < -1 THEN '<-1m'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 0 THEN '-1-0m'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN '0-1m'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN '1-3m'
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN '3-5m'
ELSE '>5m' END AS [LengthGrouping],
CASE WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth < -1 THEN 1
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 0 THEN 2
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 1 THEN 3
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 3 THEN 4
WHEN (CASE WHEN c.LastDipDepth IS NULL THEN a.ActualDepth ELSE c.LastDipDepth END) - a.TargetDrillDepth <= 5 THEN 5
ELSE 6 END AS [LengthGroupingSort]
FROM DW.DrillingEntry a
LEFT JOIN DW.SiteParameter b ON 1 = b.Id
LEFT JOIN DW.Hole c ON a.HoleId = c.Id
"]),

Had to do it 4 times for all the DB