Anonymous's avatar
Anonymous
Not applicable
8 years ago
Status:
Needs Info

On Premise Gateway Refresh Error with MYSQL datasource

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 t...
Gateways
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

Completed Ideas