Hi community,
I've got a query that when loading (rather slowly), generates 69,000 + rows however the query should only generate around 26,000.
The query imports data from SQL, and does what I think are some fairly basic transformations. Have copied the code below from the advanced editor, minus the load from SQL part to make it hopefully easier to digest.
Any insight into what is causing the load process to go so wrong is greatly appreciated!!
Thanks
Carl
let
Source = Sql.Database(
"Db_dex_prod",
"DEX",
[
Query
= "SELECT [EmployeeNo]#(lf) ,p.WamiKey#(lf) ,[ExtendedWAMI]#(lf) ,[PositionNo]#(lf) ,[PositionNextReviewDate]#(lf) ,[PositionNoSubs]#(lf) ,[PositionNoReportsTo]#(lf) ,[PositionNoFunctionalReportsTo]#(lf) ,[JobCode]#(lf) ,[JobCodeSubs]#(lf) ,[MyCareerJobCode]#(lf) ,[MyCareerJobCodeSubs]#(lf) ,[JobRosterCode]#(lf) ,[JobRosterName]#(lf) ,[ClEARFlag]#(lf) ,[OriginalCommencementDate]#(lf) ,[HireDate]#(lf) ,[CommencementDate]#(lf) ,[ExpiryDate]#(lf) ,[ContractExpiryDate]#(lf) ,[ProbationDate]#(lf) ,[TerminationDate]#(lf) ,[MonthsofService]#(lf)#(tab) ,(MonthsofService)/12 as 'YearsofService'#(lf) ,[LengthOfServiceBracket]#(lf) ,[PlacementReasonCode]#(lf) ,[PlacementReasonDesc]#(lf) ,[TerminationReasonCode]#(lf) ,[TerminationReasonDesc]#(lf) ,[EmploymentType]#(lf) ,[EmploymentTypeDesc]#(lf) ,[AttendanceType]#(lf) ,[AttendanceTypeShortDesc]#(lf) ,[AttendanceTypeDesc]#(lf) ,[AttendanceTypeSubs]#(lf) ,[AttendanceTypeShortDescSubs]#(lf) ,[AttendanceTypeDescSubs]#(lf) ,[TeamAreaCode]#(lf) ,[TeamAreaDesc]#(lf) ,[AwardHoursPerWeek]#(lf) ,[AwardHoursPerWeekDecimal]#(lf) ,[HoursPerWeek]#(lf) ,[HoursPerWeekDecimal]#(lf) ,[StandardHoursPerWeek]#(lf) ,[StandardHoursPerWeekDecimal]#(lf) ,[HoursPerFortnight]#(lf) ,[HoursPerFortnightDecimal]#(lf) ,[StandardHoursPerFortnight]#(lf) ,[StandardHoursPerFortnightDecimal]#(lf) ,[FullTimeEquivalent]#(lf)#(tab) ,CASE WHEN ISNUMERIC(FullTimeEquivalent)=1 and AttendanceType IN ('FT', 'PT') THEN CONVERT(DECIMAL(5,2),FullTimeEquivalent)#(lf)#(tab) WHEN AttendanceType LIKE 'CA%' THEN 0#(lf)#(tab) ELSE 0#(lf)#(tab) END as FullTimeEquivalent_Corrected#(lf) ,[FTEPositionCalculated]#(lf) ,[FTEEmployeeCalculated]#(lf) ,[AwardCode]#(lf) ,[AwardName]#(lf) ,[AwardLeaveEntitlements]#(lf) ,ARLEntitlement = case#(lf) when AwardName like '%4W%' then '4'#(lf) when AwardName like '%5W%' then '5'#(lf) when AwardName like '%6W%' then '6'#(lf) else ''#(lf) end#(lf)#(tab),LeaveLoading = case#(lf) when AwardName like '%14%' then '14%'#(lf) when AwardName like '%17.5%' then '17.5%'#(lf) when AwardName like '%20.83%' then '20.83%'#(lf) when AwardName like '%22.92%' then '22.92%'#(lf) when AwardName like '%25%' and AwardName not like '%CAS%' then '25.%'#(lf) when AwardName like '%27.5%' then '27.5%'#(lf) else ''#(lf) end#(lf) ,[AwardStartDate]#(lf) ,[EnterpriseAgreement]#(lf) ,[EnterpriseAgreementShortDesc]#(lf) ,[EnterpriseAgreementDesc]#(lf) ,[BaseClassSubs]#(lf) ,[BaseClassDescSubs]#(lf) ,[BaseClass]#(lf) ,[BaseClassDesc]#(lf) ,[PayPoint]#(lf) ,[PayPointDesc]#(lf) ,[HourlyPayRateAmt]#(lf) ,[AllPurposeAllowanceHourlyRateAmt]#(lf) ,[SuperHourlyRateAmt]#(lf) ,[EmployeeAnnualPayRateAmt]#(lf) ,[EmployeeSuperAnnualPayRateAmt]#(lf) ,[EmployeeParkingCharge]#(lf) ,[EmployeeParkingRate]#(lf) --,[HecsDebt]#(lf) --,[TaxCode]#(lf) --,[BSBNumber]#(lf) --,[BankAccount]#(lf) ,[FullTimeAnnualPayRateAmt]#(lf) ,[PayPeriod]#(lf) ,[KronosPayPeriod]#(lf) ,[PayPeriodDesc]#(lf) ,[PayPeriodStartDate]#(lf) ,[PayPeriodEndDate]#(lf) ,[DayOfCurrentPayPeriod]#(lf) ,[CurrentPayPeriodStartDate]#(lf) ,[CurrentPayPeriodEndDate]#(lf) ,[PayCycle]#(lf) ,[PayCompanyCode]#(lf) ,[PayCompanyInternalCode]#(lf) ,[PayCompanyPrivate]#(lf) ,[PayCompanyDesc]#(lf) ,[PayRegion]#(lf) ,[SuperCompanyCode]#(lf) ,[SuperCompanyDesc]#(lf) --,[EmployerSuperContribution]#(lf) --,[EmployeeSuperContribution]#(lf) ,[PayRuleCode]#(lf) ,[PayRuleDesc]#(lf) ,[ActualJobTitle]#(lf) ,[PositionTitle]#(lf) ,[PositionFullTitle]#(lf) ,[PositionTitleSubs]#(lf) ,[PositionFullTitleSubs]#(lf) ,[ManagerLevelType]#(lf) ,[ManagerLevelDesc]#(lf) ,[ManagerLevelTypeSubs]#(lf) ,[ManagerLevelDescSubs]#(lf) ,[LocationCode]#(lf) ,[LocationDesc]#(lf) ,[LocationGeographicalRegion]#(lf) ,[LocationGeographicalRegionSubs]#(lf) ,[PlacementCategory]#(lf) ,[PlacementCategoryDesc]#(lf) ,[PlacementClinical]#(lf) ,[PlacementClinicalCategory]#(lf) ,[PlacementCategorySubs]#(lf) ,[PlacementCategoryDescSubs]#(lf) ,[PlacementClinicalSubs]#(lf) ,[PlacementClinicalCategorySubs]#(lf) ,[EmployeeStatusCode]#(lf) ,[CostingEffectiveDate]#(lf) ,[CostCentreCode]#(lf) ,[CostCentreDesc]#(lf) ,[Branch]#(lf) ,[BranchDesc]#(lf) ,[CompanyCode]#(lf) ,[CompanyDesc]#(lf) ,[FinanceStreamCode]#(lf) ,[FinanceStreamDesc]#(lf) ,[HRStreamCode]#(lf) ,[HRStreamDesc]#(lf) ,[OrgUnitNo]#(lf) ,[OrgUnitNoSubs]#(lf) ,[OrgUnitDesc]#(lf) ,[OrgUnitDescSubs]#(lf) ,[OrgDivision]#(lf) ,[TerminationCreatedDate]#(lf) ,[TerminationEffectiveDate]#(lf) ,[DaysSinceTerminationEffected]#(lf) ,[DaysSinceTerminatedPayPeriod]#(lf) ,[HolidayRegionCode]#(lf) ,[HolidayRegionDesc]#(lf) ,[MaterEducation]#(lf)#(tab) ,[Title]#(lf)#(tab) ,[FullName]#(lf) ,[DateOfBirth]#(lf)#(tab) ,[Age]#(lf) ,[AgeBracket]#(lf)#(tab) ,[Gender]#(lf)#(tab) ,[Postcode]#(lf)#(tab) ,[PostalCountryDesc]#(lf) ,[PrivateEmailAddress]#(lf) ,[WorkEmailAddress]#(lf) ,[CurrentPosnCnt]#(lf) ,[PaidPositionCnt]#(lf) ,[MultipleEmps]#(lf) FROM [DEX].[Employee].[Placement] p#(lf) LEFT JOIN [DEX].[Employee].[PersonalDetails] e#(lf) ON p.[WamiKey] = e.[WamiKey]#(tab)#(lf)WHERE EmployeeNo NOT LIKE 'T%'#(lf)AND PlacementCategoryDesc NOT LIKE 'Student%'#(lf)AND NOT (EmployeeStatusCode = 'FUTURE');"
]
),
#"Extracted Date" = Table.TransformColumns(
Source,
{
{"TerminationDate", DateTime.Date, type date},
{"ProbationDate", DateTime.Date, type date},
{"ContractExpiryDate", DateTime.Date, type date},
{"ExpiryDate", DateTime.Date, type date},
{"CommencementDate", DateTime.Date, type date},
{"HireDate", DateTime.Date, type date},
{"OriginalCommencementDate", DateTime.Date, type date},
{"PositionNextReviewDate", DateTime.Date, type date},
{"AwardStartDate", DateTime.Date, type date},
{"PayPeriodStartDate", DateTime.Date, type date},
{"PayPeriodEndDate", DateTime.Date, type date},
{"CurrentPayPeriodStartDate", DateTime.Date, type date},
{"CurrentPayPeriodEndDate", DateTime.Date, type date},
{"CostingEffectiveDate", DateTime.Date, type date},
{"TerminationEffectiveDate", DateTime.Date, type date},
{"DateOfBirth", DateTime.Date, type date},
{"TerminationCreatedDate", DateTime.Date, type date}
}
),
#"Changed Type1" = Table.TransformColumnTypes(#"Extracted Date", {{"ARLEntitlement", Int64.Type}}),
#"Changed Type" = Table.TransformColumnTypes(
#"Changed Type1",
{
{"HireDate", type date},
{"PositionNextReviewDate", type date},
{"OriginalCommencementDate", type date},
{"CommencementDate", type date},
{"ExpiryDate", type date},
{"ContractExpiryDate", type date},
{"ProbationDate", type date},
{"TerminationDate", type date},
{"AwardStartDate", type date},
{"PayPeriodStartDate", type date},
{"PayPeriodEndDate", type date},
{"CurrentPayPeriodStartDate", type date},
{"CurrentPayPeriodEndDate", type date},
{"CostingEffectiveDate", type date},
{"TerminationCreatedDate", type date},
{"TerminationEffectiveDate", type date}
}
),
PersonStatus = Table.AddColumn(
#"Changed Type",
"PersonStatus",
each
if [HireDate] > DateTime.Date(DateTime.LocalNow()) then
"New Hire"
else if [TerminationDate] = null then
"Existing"
else if [TerminationDate] > DateTime.Date(DateTime.LocalNow()) then
"Existing"
else
"Leaver"
),
FormerStudent = Table.AddColumn(
PersonStatus,
"FormerStudent",
each if List.Contains(fact_StudentData[StudentWAMI], [WamiKey]) then "Y" else "N"
),
NursingGrade = Table.AddColumn(
FormerStudent,
"NursingGrade",
each
if [HRStreamDesc] <> "Nursing" then
null
else if List.ContainsAny({[BaseClass]}, {"AN", "ANCSD"}) then
"Assistant in Nursing"
else if List.ContainsAny({[BaseClass]}, {"EN", "ENAP", "ENNM"}) then
"Enrolled Nurse"
else if List.ContainsAny(
{[BaseClass]},
{"NM1", "NM2", "NM3", "RN1", "RN2", "RN3", "CQRN4", "NP"}
)
then
"Registered Nurse / Midwife"
else
null
),
ContractedHoursPerWeek_Corrected = Table.AddColumn(
NursingGrade,
"ContractedHoursPerWeek_Corrected",
each if [AttendanceTypeDesc] = "Casual" then 0 else [StandardHoursPerWeekDecimal]
),
#"BaseRate(APAIncluded)" = Table.AddColumn(
ContractedHoursPerWeek_Corrected,
"BaseRate(APAIncluded)",
each Number.Round(
if [AllPurposeAllowanceHourlyRateAmt] = 0 then
[HourlyPayRateAmt]
else
([HourlyPayRateAmt] + [AllPurposeAllowanceHourlyRateAmt]),
4
)
),
#"Changed Type3" = Table.TransformColumnTypes(
#"BaseRate(APAIncluded)",
{{"BaseRate(APAIncluded)", Currency.Type}}
),
LOS_SortOrder = Table.AddColumn(
#"Changed Type3",
"LOS_SortOrder",
each
if [LengthOfServiceBracket] = "0 - 6 Months" then
1
else if [LengthOfServiceBracket] = "7 - 12 Months" then
2
else if [LengthOfServiceBracket] = "1 - 2 Years" then
3
else if [LengthOfServiceBracket] = "3 - 5 Years" then
4
else if [LengthOfServiceBracket] = "6 - 10 Years" then
5
else if [LengthOfServiceBracket] = "11 - 15 Years" then
6
else if [LengthOfServiceBracket] = "16 - 20 Years" then
7
else if [LengthOfServiceBracket] = "21 - 30 Years" then
8
else
9
),
#"Replaced Value" = Table.ReplaceValue(
LOS_SortOrder,
"Under 18",
"17 and younger",
Replacer.ReplaceText,
{"AgeBracket"}
),
ELV_Concept = Table.AddColumn(
#"Replaced Value",
"ELV_Concept",
each
if [PlacementClinical] = "Yes" and [MonthsofService] = 0 then
"-100%"
else if [PlacementClinical] = "Yes" and [MonthsofService] < 1 then
"-50%"
else if [PlacementClinical] = "Yes" and [MonthsofService] < 2 then
"0%"
else if [PlacementClinical] = "Yes" and [MonthsofService] < 6 then
"50%"
else if [PlacementClinical] = "Yes" and [MonthsofService] > 12 then
"100%"
else if [MonthsofService] = 0 then
"-100%"
else if [MonthsofService] < 2 then
"-50%"
else if [MonthsofService] < 6 then
"0%"
else if [MonthsofService] < 13 then
"50%"
else
"100%"
),
#"Changed Type2" = Table.TransformColumnTypes(ELV_Concept, {{"ELV_Concept", Percentage.Type}}),
ValueToOrganisation = Table.AddColumn(
#"Changed Type2",
"ValueToOrganisation",
each
if [PlacementClinical] = "Yes" and [MonthsofService] < 2 then
"Negative"
else if [PlacementClinical] = "Yes" then
"Positive"
else if [MonthsofService] < 6 then
"Negative"
else
"Positive"
),
#"Replaced Value1" = Table.ReplaceValue(
ValueToOrganisation,
"Yes",
"Clinical",
Replacer.ReplaceText,
{"PlacementClinical"}
),
#"Replaced Value2" = Table.ReplaceValue(
#"Replaced Value1",
"No",
"Non-Clinical",
Replacer.ReplaceText,
{"PlacementClinical"}
),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value2", {{"ELV_Concept", - 1}}),
#"Replaced Errors1" = Table.ReplaceErrorValues(
#"Replaced Errors",
{{"ValueToOrganisation", "Negative"}}
),
Generation = Table.AddColumn(
#"Replaced Errors1",
"Generation",
each
if Date.Year([DateOfBirth]) < 1945 then
"The Silent Generation"
else if Date.Year([DateOfBirth]) < 1964 then
"Baby Boomers"
else if Date.Year([DateOfBirth]) < 1980 then
"Generation X"
else if Date.Year([DateOfBirth]) < 1995 then
"Millennials"
else if Date.Year([DateOfBirth]) < 2010 then
"Generation Z"
else
"Generation Alpha"
),
#"Replaced Errors2" = Table.ReplaceErrorValues(Generation, {{"Generation", null}}),
EmployedElsewhere = Table.AddColumn(
#"Replaced Errors2",
"EmployedElsewhere?",
each if List.Contains(dim_ActiveWAMIs[WamiKey], [WamiKey]) then "Yes" else "No"
)
in
EmployedElsewhere
