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

[tompearson4]

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.

[Jimmy801]

Hello @tompearson4 

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

BR

Jimmy

[tompearson4]

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.

[Jimmy801]

Hello @tompearson4 

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

BR

Jimmy

[tompearson4]

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

[Jimmy801 · Accepted solution]

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

[tompearson4]

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!

[Jimmy801]

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

BR

Jimmy

[ValeriaBreve]

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!