Desktop dynamic mysql query with parameters works; In service DOES NOT

[Anonymous]

I have a problem that only occurs with the service and not the desktop.  I have this dynamic mysql query that uses 2 parameters: date and hour.  I changed some of the names of the values like the host and db just for this post but basically the setup looks like this:

let

table_name = if Text.Contains(date, "YYYYMMDD") then "HistoryTable" else Text.Combine({"HistoryTable_", date}),
sql_query = "SELECT * #(lf)FROM " & table_name & " #(lf)WHERE DATE_FORMAT(FROM_UNIXTIME(lms), ""%i"") = '00' #(lf)AND DATE_FORMAT(FROM_UNIXTIME(lms), ""%H"") in ('" & hour & "')",
Source = MySQL.Database("my.fakedomain.net", "mydb", [ReturnSingleDatabase=true, Query=sql_query]),

...

#"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"City.1", "City"}})

in

#"Renamed Columns3"

The desktop version used to get this error:

Formula.Firewall: Query 'FW' (step 'Renamed Columns3') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Until I relaxed the privacy restrictions and prompting according to these posts:

https://simplebiinsights.com/power-bi-issue-in-using-query-list-parameter/

https://community.powerbi.com/t5/Desktop/Permission-is-required-to-run-this-native-database-query/td...

The service / web based dataset upon refresh would always fail with this message:

[Unable to combine data] Section1/FW/Renamed Columns3 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

This looks really identical to what I originally got in the desktop.  How do I resolve this and likewise relax the privacy level restriction?  The above was from MyWorkspace.  As a comparison to using a Premium Content workspace I get this variation:

{"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"[Unable to combine data] Section1/FW/Renamed Columns3 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"MySql\",\"path\":\"mydb.fakedomain.net;mydb\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"PrivacyError"}}],"exceptionCulprit":1}}} Table: FW.

Please advise on how to make the refresh work with the parameters and dynamic mysql query with the service. 

Thanks in advance!

[v-eqin-msft]

Hi @Anonymous ,

Please take a look at this blog for reference.

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Anonymous]

Thanks Eyelyn but the article seems to be not so clear with regards to my situation.  The post you provided is Excel and mine issue is with MySQL.  

Furthermore, my primary point is that the desktop version allows my query without issue. It only is the service that the problem presents itself. Makes me think that the code or parser on the service is either more strict or has not been updated to reflect the desktop.

Your post states the combine problem comes about when there is an “External Data Source” and “Another Query”.  I ask, which is the “Another Query” in my query?  I only have 1 SQL query albeit, it’s dynamic.  Could it be that the mere fact that the variable DimShipper is introduce makes the service parser believe that is the "Another Query"?  Just because it's a variable??? 

If so, I can not circumvent this because I have then 2 “Another Queries”.  I have the “table_name” and the “hour” which is necessary for the sql.  I have made a workaround by creating a dynamic view that the user updates on a separate CGI page I made but I would like to find out what specifically is wrong or if it is indeed a code update that needs to take place on the service.

The other “functions” by the way I use after the Source assignment are as follows:

 Table.DuplicateColumn

 Table.RemoveColumns

 Table.RenameColumns

 Table.ReplaceValue

 Table.Sort

 Table.SplitColumn

 Table.TransformColumnTypes

Any further suggestions or insights would be greatly appreciated but I think my issue is not apples to apples at least with that Excel post and the issue being the discrepancy between the desktop version and servcie  The service and desktop, in my opinion, should have the same behavior.

Thanks!

Bryant

[Anonymous]

Okay, I narrowed it down that the service does not allow a parameter as the table.  I removed all other functions and only have the below which still has the combine error.  It does not like that I concatenate the date parameter with the prefix of the table name.  I need that though because it is the only way to allow the user to select the desired table.  My workaround in case anyone is interest was just put a cgi page that allows the date to be selected then I dynamically CREATE OR REPLACE VIEW and use that view in the query.  

I think that the code on the service needs updating and is not in sync with the desktop being too restrictive here.

let

sql_query = "SELECT * #(lf)FROM HistoryTable_" & date & " #(lf)WHERE DATE_FORMAT(FROM_UNIXTIME(lms), ""%i"") = '00' #(lf)AND DATE_FORMAT(FROM_UNIXTIME(lms), ""%H"") in ('00')",
Source = MySQL.Database("mydb.fakedomain.net", "mydb", [ReturnSingleDatabase=true, Query=sql_query])

in

Source

Thanks,

Bryant

[v-eqin-msft]

Hi @Anonymous ,

Hope these help😀

https://social.technet.microsoft.com/Forums/en-US/4e409d5b-260d-4b6c-baca-10cc94d61df5/formulafirewall-query-x-step-source-references-other-queries-or-steps-and-so-may-not?forum=powerquery

https://community.powerbi.com/t5/Desktop/Formula-Firewall-Query-references-other-queries-or-steps/td-p/150725

Best Regards,
Eyelyn Qin

[Anonymous]

Wow, it looks like this went full circle to my original post.  No, those don't help.  The first one is for Excel.  The second one is what I explained I did in my original post for the desktop but *DOES NOT* apply in the service when publishing.  Please re-read the entire posts and threads again to fully understand my issue.  Basically desktop allows you to relax the privacy level while the service still has code that restricts it and has no tuneables to relax it.

Thanks!

Bryant