topic Re: Data is 'disappearing' between Power Query and Data View. in Power Query

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

tompearson4 — Mon, 12 Oct 2020 14:08:41 GMT

Hi,

As in the title, I'm having an issue where my data is disappearing between Power Query and Data View after applying queries. All of the data is loaded as expected in the query editor, I have run through the steps to check for errors and refreshed the preview to ensure that it is up to date. Though when I apply the queries, the tables are showing with 0 rows of data.

I have checked that I am not applying filters anywhere or "keeping errors". The query built from the direct import of the data source is loading correctly, though the queries built from a reference of the 'import query' are the ones which are experiencing the issues. I'm sure that the solution is something simple that I am missing, but after investigation I'm just not sure what it is! Please could somebody suggest what the issue could be and how I can fix it?

Additional info: I have also copied the offending query, the query it references and all that reference it into a separate Power BI file and it appears to be loading fine in there, but not in the original file.

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

Jimmy801 — Mon, 12 Oct 2020 14:22:24 GMT

Hello tompearson4

could you post the M-code and make some screenshot of Power Query and the data view in Power Bi

Jimmy

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

tompearson4 — Mon, 12 Oct 2020 14:54:55 GMT

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"

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

Jimmy801 — Mon, 12 Oct 2020 16:31:27 GMT

Hello tompearson4

and there is no filter applied in the data view? And the load is enabled in Power Query?

Jimmy

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

tompearson4 — Tue, 13 Oct 2020 09:01:38 GMT

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!)

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

Jimmy801 — Tue, 13 Oct 2020 09:45:07 GMT

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

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

tompearson4 — Tue, 13 Oct 2020 10:57:51 GMT

Thanks 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!

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

Jimmy801 — Tue, 13 Oct 2020 11:12:07 GMT

Hello tompearson4

I've just added a Table.Buffer to your last step. This sometimes solves some problems when loading or sorting.

So fine that this was helping you

Jimmy

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

ValeriaBreve — Mon, 02 Jan 2023 14:35:22 GMT

Same issue for me - I had dates showing perfectly in PowerQuery and loading blank in the data model. The Table.Buffer solved the issue. Thanks!

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

pipkinp86 — Wed, 31 Jul 2024 15:57:07 GMT

wow! it worked like magic! thank you so much!