On Premise Gateway Refresh Error with MYSQL datasource

 If my dataset does not have a AWS RDS MYSQL datasource then it works fine with On Premise Gateway Refresh.

Anonymous,

Are you able to refresh the data in Power BI Desktop? Do you merge tables of different data sources in Power BI Desktop? If so, could you please share M code of your queries in Power BI Desktop here?

By the way, do you also add the Azure SQL data source within configured gateway?

There is also a similar thread for your reference.
https://community.powerbi.com/t5/Integrations-with-Files-and/gateway-refresh-error-quot-references-other-queries-please/m-p/496841#M19245

Regards,
Lydia

Are you able to refresh the data in Power BI Desktop? Yes

Do you merge tables of different data sources in Power BI Desktop? Yes. MYSQL and Excel 

let
StartDate = Text.From(FilterStartDate),
EndDate = Text.From(FilterEndDate),
Source = MySQL.Database("dw.qualbe.com", "datawarehouse1", [ReturnSingleDatabase=true, Query="SELECT #(lf) agent_name, #(lf) count(*) as calls#(lf)FROM #(lf) inContact_View #(lf)WHERE #(lf)start_date >= date_format(STR_TO_DATE('"&StartDate&"', '%c/%d/%Y'),'%Y-%m-%d') #(lf)AND start_Date <= date_format(STR_TO_DATE('"&EndDate&"', '%c/%d/%Y'),'%Y-%m-%d') #(lf) AND agent_name <> 'N/A' #(lf) AND disp_code <> 3692 #(lf) AND disp_code <> 10080 #(lf) AND (skill_name = 'Sales') #(lf) AND contact_id #(lf) NOT IN #(lf) (#(lf) SELECT contact_id #(lf) FROM inContact_View #(lf) WHERE pocName in ('M1Dental.com','Tejas Radio'#(lf) )#(lf) ) #(lf)AND contact_id #(lf) NOT IN #(lf) (#(lf) SELECT contact_id #(lf) FROM inContact_View #(lf) WHERE #(lf) (disp_name = 'Transfer - Careington' OR disp_name = 'No Sale - Member' OR disp_name = 'Transfer - Customer Care') #(lf) AND#(lf) (#(lf) concat('',trim(REPLACE(Disp_Comments, '\t', '' )) * 1) = trim(REPLACE(Disp_Comments, '\t', '' ))#(lf) or #(lf) REPLACE(Disp_Comments, '\t', '' ) regexp '^[0-9]+$'#(lf) )#(lf) AND skill_name = 'Sales'#(lf) )#(tab)#(lf)AND contact_id #(lf) NOT IN #(lf) (#(lf) SELECT contact_id #(lf) FROM inContact_View #(lf) WHERE pocName IS NOT NULL #(lf) AND disp_name = 'N/A' #(lf) AND agent_time < 30#(lf) ) #(lf)AND contact_id #(lf) NOT IN #(lf) (#(lf) SELECT contact_id #(lf) FROM inContact_View #(lf) WHERE disp_name = 'Voicemail' #(lf) ) #(lf)GROUP BY agent_name #(lf)"]),
#"Renamed Columns" = Table.RenameColumns(Source,{{"Agent_Name", "Employee"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns",{"Employee"},SalesCallCountAdjustment,{"Employee"},"SalesCallCountAdjustment",JoinKind.LeftOuter),
#"Expanded SalesCallCountAdjustment" = Table.ExpandTableColumn(#"Merged Queries", "SalesCallCountAdjustment", {"CallCount", "CommissionPercentage", "CommissionAmount"}, {"SalesCallCountAdjustment.CallCount", "SalesCallCountAdjustment.CommissionPercentage", "SalesCallCountAdjustment.CommissionAmount"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded SalesCallCountAdjustment",null,0,Replacer.ReplaceValue,{"SalesCallCountAdjustment.CallCount"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,0,Replacer.ReplaceValue,{"SalesCallCountAdjustment.CommissionPercentage"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,0,Replacer.ReplaceValue,{"SalesCallCountAdjustment.CommissionAmount"}),
#"Grouped Rows" = Table.Group(#"Replaced Value2", {"Employee"}, {{"AdjustedCallCount", each List.Sum([SalesCallCountAdjustment.CallCount]), type number}, {"CallCount", each List.Max([calls]), type number}, {"AdjCommPercentage", each List.Sum([SalesCallCountAdjustment.CommissionPercentage]), type number}, {"AdjCommAmount", each List.Sum([SalesCallCountAdjustment.CommissionAmount]), type number}}),
#"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"AdjustedCallCount", Int64.Type}}),
FinalCallCount = Table.AddColumn(#"Changed Type", "FinalCallCount", each [CallCount] + [AdjustedCallCount]),
#"Changed Type1" = Table.TransformColumnTypes(FinalCallCount,{{"FinalCallCount", Int64.Type}})
in
#"Changed Type1"

By the way, do you also add the Azure SQL data source within configured gateway? Yes

Anonymous,

Could you please share the Advanced Editor code of TimeSheet table?  What is the AutoRemovedColumns1 step in the query?

Regards,
Lydia

Anonymous

i put all my M code in this word document in One Drive. Let me know if you have trouble accessing it.

https://qualbe-my.sharepoint.com/:w:/p/schowla/ET_c6wen5R9Ks7EnK1YI5V0Bb_VGYycwmijNWBSWQmNeXg?e=ahBLL8

Wierd thing is that in the TimeSheet query i do not have that step named 'AutoRemovedColumns1'. Infact i do not have a step names in my whole PowerBI file.

Thanks for all your support.

Anonymous,

It is strange. You are able to refresh data in Power BI Desktop, right? Are you able to use personal mode gateway to refresh the dataset in Power BI Service?

Regards,
Lydia

You are able to refresh data in Power BI Desktop, right?

Yes.

Are you able to use personal mode gateway to refresh the dataset in Power BI Service?

Yes. I was able to use personal gateway, but it failed with the same error message. So did not work with personal gateway.

Anonymous,

I would recommend you submit a ticket in Power BI support site.


Regards,
Lydia