Unable to Combine Data and Cannot Convert to Value - Cannot Schedule Refresh

[Anonymous]

Hi,

I'm having some problems scheduling a refresh on my report.

I'm using a number of parameters to pass values through the SQL statements. Here's an example of the query:

let
    dbTable = fnGetParameter("TARP Table"),
    sFilterField = fnGetParameter("Filter Field"),
    sFieldValue = Text.From(fnGetParameter("ClientParentID")),
    sDateField = fnGetParameter("Date Field"),
    sStartDate = fnGetParameter("StartDate"),

    dbQuery = "SELECT #(lf)#(lf)adDuration,#(lf)advertiserIsCompetitorFlag,#(lf)audience,#(lf)competitiveSet_DESC,#(lf)clientParent_DESC,#(lf)size,#(lf)tarps,#(lf)dayDate,#(lf)demographic_ID,#(lf)NSN_Product_DESC,#(lf)productParent,#(lf)NSN_Market_DESC,#(lf)clientParent_ID#(lf)#(lf) 
    FROM " & dbTable & " WHERE " & sFilterField & "='" & sFieldValue & "'" & " AND " & sDateField & ">='" & sStartDate & "'",

    Source = Sql.Database("auidcsqlprod","dawa",[Query=dbQuery]),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"adDuration", "Ad Duration"}, {"advertiserIsCompetitorFlag", "Competitor Flag"}, {"audience", "Audience"}, {"competitiveSet_DESC", "Competitive Set"}, {"clientParent_DESC", "Client Parent"}, {"size", "Size"}, {"tarps", "TARPs"}, {"dayDate", "Date"}, {"demographic_ID", "Demographic ID"}, {"NSN_Product_DESC", "Product"}, {"productParent", "Product Parent"}, {"NSN_Market_DESC", "Market"}, {"clientParent_ID", "Client Parent ID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Replaced 'no' with 'Client'" = Table.ReplaceValue(#"Changed Type","no","Client",Replacer.ReplaceText,{"Competitor Flag"}),
    #"Replaced 'yes' with 'Competitor'" = Table.ReplaceValue(#"Replaced 'no' with 'Client'","yes","Competitor",Replacer.ReplaceText,{"Competitor Flag"}),
    #"Added Competitive Colour Column" = Table.AddColumn(#"Replaced 'yes' with 'Competitor'", "Competitive Colour", each if [Competitor Flag] = "Client" then 1 else 0,Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Competitive Colour Column",{"Demographic ID"},Demographic,{"tvrDemographicId"},"Demographic",JoinKind.LeftOuter),
    #"Expanded Demographic" = Table.ExpandTableColumn(#"Merged Queries", "Demographic", {"name"}, {"Demographic"}),
    #"Removed ' Television' from Market" = Table.ReplaceValue(#"Expanded Demographic"," Television","",Replacer.ReplaceText,{"Market"})

in
    #"Removed ' Television' from Market"

So the 5 parameters allow me to build form part of dbQuery, which then feeds into [Query=dbQuery]. Combined with the Server and Database information I'm able to make the query dynamic. The report refreshes fine in Desktop without any issues.

However, when I go to schedule a refresh using our Gateway on the Service I'm able to set it up fine but it falls over with 2 different issues. I've attached screenshots of these:

Does anyone have any idea what I need to do? I've read that it may be something to do with my parameters but I don't understand what they're saying or how to amend my report so that my parameters remain and a refresh can be scheduled.

Please help!

Mark

[v-piga-msft]

Hi @Anonymous，

For your first error message, please refer to this similar thread which has been solved.

For your second error message, it seems that the error is caused by your combine data in Power Query. Since you could refresh successly in Power BI Desktop but failed in Power BI Service which may cuased by the Gateway.

You may should update your gateway to be the latest firstly.

Best Regards,

Cherry

[Anonymous]

Hi @v-piga-msft,

I've checked my permissions levels globally and on current file to make sure they're all the same, which they are. I've also checked my Privacy setting under Options. Here's what I have:

I've tested this earlier by having 2 simple datasets from the 2 different data sources - the scheduled refresh works fine.

I think the issue may now in my query set up. I'm using a number of queries to pass values through a SQL statement - the aim being to use Parameters to query the SQL statement. In all my previous testing this has worked fine too.

Here's an example of the query I'm using:

let
    dbTable = fnGetParameter("Client Spend Table"),
    sFilterField = fnGetParameter("Filter Field"),
    sFieldValue = Text.From(fnGetParameter("ClientParentID")),
    sDateField = fnGetParameter("Date Field"),
    sStartDate = fnGetParameter("StartDate"),

dbQuery = "SELECT #(lf)#(lf)BCC_clientCostToClient,#(lf)BCC_commissionRebate,#(lf)BCC_CTCGST,#(lf)BCC_CTCNoGST,#(lf)BCC_spendClient,#(lf)BCC_spendMedia,#(lf)BCC_spendPlusLoading,#(lf)clientBrand_DESC,#(lf)clientDivision_DESC,#(lf)clientMasterProduct_DESC,#(lf)clientProduct_DESC,#(lf)clientParent_DESC,#(lf)clientSubBrand_DESC,#(lf)dayDate,#(lf)id,#(lf)OMG_masterMarketType_DESC,#(lf)OMG_masterMediaType_DESC,#(lf)OMG_mediaType_DESC,#(lf)OMG_state_DESC,#(lf)clientParent_ID#(lf)#(lf)

FROM " & dbTable & " WHERE " & sFilterField & "='" & sFieldValue & "'" & " AND " & sDateField & ">='" & sStartDate & "'",

Source = Sql.Database("auidcsqlprod","dawa",[Query=dbQuery]),

The top part in green pulls in all the queries parameters I need - this all appears in a table of results, combining parameters values. The bit in blue are the columns I want from the query. Then using the values in green I build the FROM and WHERE statements - all under dbQuery. So then when it comes to Source I build by query by giving it the Server, Database and SQL statement.

We've not updated our Gateway in months, so there's been no change to it that could have affected this.

I'm really struggling to figure this out.