On Premise Gateway Refresh Error with MYSQL datasource

[Anonymous]

DataSources:

Excel, CSV, Azure SQL DB, AWS RDS MYSQL

Privacy Level Setting:

Organizational

Power BI Desktop Version:

Works fine.

Power BI Service:

get below error when i try to refresh using an On-Premise Gateway.

Underlying error code:	-2147467259 Table: TimeSheet.
Underlying error message:	[Unable to combine data] Section1/TimeSheet/AutoRemovedColumns1 references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.MashupSecurityException.DataSources:	[{"kind":"MySql","path":"dw.qualbe.com;datawarehouse1"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:	PrivacyError
Cluster URI:	WABI-US-NORTH-CENTRAL-redirect.analysis.windows.net
Activity ID:	97f085b8-1b79-823b-726c-ae11cbaafd63
Request ID:	0c7a41e1-5655-7e10-b415-2ba23998ea0c
Time:	2018-08-29 00:34:51Z

[Anonymous]

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

[Anonymous]

@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

[Anonymous]

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]

@Anonymous,

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

Regards,
Lydia

[Anonymous]

@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]

@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

[Anonymous]

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]

@Anonymous,

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


Regards,
Lydia