Data is 'disappearing' between Power Query and Data View.

Hello tompearson4 

strange behavious, maybe some bug. Can you test this code here please

let
Source = #"Sector Population Import",
#"Removed Other Columns" = Table.SelectColumns(Source,{"Data Date Stamp", "Unique Identifier", "Date of birth", "Grade", "Function", "Company Start Date", "Actual Grade", "Cost Centre", "Source", "Worker Class", "Skillset Code", "FTE", "Location Code"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Data Date Stamp", type date}}),
#"Renamed Columns3" = Table.RenameColumns(#"Changed Type",{{"Cost Centre", "Cost Centre Original"}}),
#"Cost Centre Manipulate" = Table.AddColumn(#"Renamed Columns3", "Cost Centre", each if [Source] = "KSA" then "KSA::" & [Cost Centre Original] else [Cost Centre Original]),
#"Add StatusDate Column" = Table.AddColumn(#"Cost Centre Manipulate", "StatusDate", each Date.AddDays([Data Date Stamp], -15)),
#"Manipulate to Latest Month" = Table.ReplaceValue(#"Add StatusDate Column",#date(2020, 1, 9),#date(2020, 5, 9),Replacer.ReplaceValue,{"StatusDate"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Manipulate to Latest Month",{{"StatusDate", type date}}),
#"Calculated End of Month" = Table.TransformColumns(#"Changed Type1",{{"StatusDate", Date.EndOfMonth, type date}}),
#"Source - SF Home to TM1" = Table.ReplaceValue(#"Calculated End of Month","SF Home","TM1",Replacer.ReplaceText,{"Source"}),
#"Source - SF Host to TM1" = Table.ReplaceValue(#"Source - SF Home to TM1","SF Host","TM1",Replacer.ReplaceText,{"Source"}),
#"Source - SF GM Home to TM1" = Table.ReplaceValue(#"Source - SF Host to TM1","SF GM Home","TM1",Replacer.ReplaceText,{"Source"}),
#"Source - SF Sec Host to TM1" = Table.ReplaceValue(#"Source - SF GM Home to TM1","SF Sec Host","TM1",Replacer.ReplaceText,{"Source"}),
#"Added Custom" = Table.AddColumn(#"Source - SF Sec Host to TM1", "Status", each "SecPop"),
#"Skillset Code Extract" = Table.AddColumn(#"Added Custom", "Text Before Delimiter", each if [Source] = "KSA" then "KSA::" & Text.BeforeDelimiter([Skillset Code], " ") else Text.BeforeDelimiter([Skillset Code], " "), type text),
#"Skillset Code Extract 2" = Table.TransformColumns(#"Skillset Code Extract", {{"Text Before Delimiter", each Text.BeforeDelimiter(_, "-"), type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Skillset Code Extract 2",{{"Skillset Code", "Skillset Code Long"}, {"Text Before Delimiter", "Skillset Code"}}),
#"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Source", "Worker Class"}, Source_LookupValues, {"Source", "Worker Class"}, "LookupAttrition", JoinKind.LeftOuter),
#"Expanded LookupAttrition" = Table.ExpandTableColumn(#"Merged Queries", "LookupAttrition", {"Annual Attrition", "Avg_Retirement_Age", "Exit Within N Years", "StdDev_Ret"}, {"Annual Attrition", "Avg_Retirement_Age", "Exit Within N Years", "StdDev_Ret"}),
#"Replaced Start Date Errors to null" = Table.ReplaceErrorValues(#"Expanded LookupAttrition", {{"Company Start Date", null}}),
#"Replaced DoB Errors to null" = Table.ReplaceErrorValues(#"Replaced Start Date Errors to null", {{"Date of birth", null}}),
#"Merged Queries1" = Table.NestedJoin(#"Replaced DoB Errors to null", {"Function"}, #"TPSP Function Annual Attrition", {"Function"}, "TPSP Function Annual Attrition", JoinKind.LeftOuter),
#"Expanded TPSP Function Annual Attrition" = Table.ExpandTableColumn(#"Merged Queries1", "TPSP Function Annual Attrition", {"Function Annual Attrition"}, {"Function Annual Attrition"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded TPSP Function Annual Attrition",{{"Function Annual Attrition", "TPSP Function Annual Attrition"}}),
#"Merged Queries2" = Table.NestedJoin(#"Renamed Columns1", {"Skillset Code Long"}, #"TPSP Skillset Annual Attrition", {"Skillset Code"}, "TPSP Skillset Annual Attrition", JoinKind.LeftOuter),
#"Expanded TPSP Skillset Annual Attrition" = Table.ExpandTableColumn(#"Merged Queries2", "TPSP Skillset Annual Attrition", {"Annual Attrition"}, {"Annual Attrition.1"}),
#"Renamed Columns2" = Table.RenameColumns(#"Expanded TPSP Skillset Annual Attrition",{{"Annual Attrition.1", "TPSP Skillset Annual Attrition"}}),
#"Changed Type2" = Table.Buffer(Table.TransformColumnTypes(#"Renamed Columns2",{{"TPSP Function Annual Attrition", type number}, {"TPSP Skillset Annual Attrition", type number}}))
in
#"Changed Type2"

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy