Forum Discussion
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
Hi Jimmy801, the M-code & screenshots are below, apologies if it's messy!
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.TransformColumnTypes(#"Renamed Columns2",{{"TPSP Function Annual Attrition", type number}, {"TPSP Skillset Annual Attrition", type number}})
in
#"Changed Type2"
Screenshot of the Power Query editor window.
Screenshot of the Data View of the same query.
Hello tompearson4
and there is no filter applied in the data view? And the load is enabled in Power Query?
BR
Jimmy
Hi Jimmy801,
I've checked across a number of the data view tables to see if any filters are applied & I can't find any. Also the "Clear all filters" option is greyed out, implying there aren't any filters active.
Load is definitely enabled, I have also tried disabling & re-enabling load on the query to see if this worked (it didn't!)
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
JimmyThanks a lot Jimmy801 - I've loaded the code below & it works fine now.
What parts were changed specifically in the code below & do you know why they would have prevented the data from appearing in the table? I've noticed I'm having the same issue with a few of the other tables/queries in my dataset too so would be good to sort those out as well!