Dataflow - query doesn't show in PBI desktop if query has a change type to date step

[Anonymous]

I have a dataflow with 6 entities/queries. Each one pulls a simple table from Oracle. I used to be able to see all 6 queries when I connect to the dataflow in PBI desktop until today.  Today, I am not seeing this query (pasted M code below). Why is this suddenly not showing?  The others still are.

M Code:

let

Source = Oracle.Database("r12prod.corp.yodlee.com:1521/PROD", [HierarchicalNavigation=true, Query="select#(lf)FFV.FLEX_VALUE ""GL Cust#"",#(lf)FFV.ATTRIBUTE4 ""AR Cust#"",#(lf)FFV.DESCRIPTION ""Customer Name"",#(lf)FFV.summary_Flag ""Parent"",#(lf)FFV.enabled_Flag ""Enabled"",#(lf)FFV.ATTRIBUTE3 ""Channel"",#(lf)FFV.ATTRIBUTE5 ""Customer Type"",#(lf)FTT.territory_short_name as ""Country Name"",#(lf)--FFV.ATTRIBUTE6 ""Country"",#(lf)FFV.ATTRIBUTE7 ""Start Date"",#(lf)FFV.ATTRIBUTE8 ""End Date""#(lf)#(lf)from FND_FLEX_VALUES_VL FFV left outer join#(lf)FND_TERRITORIES_TL FTT #(lf)on FFV.ATTRIBUTE6=FTT.TERRITORY_CODE#(lf)where FFV.FLEX_VALUE_SET_ID = '1009632'#(lf)order by 1"]),

#"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([#"GL Cust#"], "T")),

#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Start Date", each Text.BeforeDelimiter(_, " "), type text}, {"End Date", each Text.BeforeDelimiter(_, " "), type text}}),

#"Changed Type" = Table.TransformColumnTypes(#"Extracted Text Before Delimiter", {{"Start Date", type date}, {"End Date", type date}})

in

#"Changed Type"

Thanks,

Ferdinand

[venal]

@Anonymous @TheBeeGuy 

Please refer the link.

If this post helps, then please consider Accept it as the solution to help the other members find it more
If this post was helpful may I ask you to mark it as solution and click on thumb symbol?

[TheBeeGuy]

Thanks! Converting date columns to date/time fixed my missing entities as well.

[Anonymous · Accepted solution]

Thanks for the link @venal .  Now I understand that the real issue is not the change type transformation step.  The issue is that a date type column is showing as "Unclassified" after saving the dataflow.  My current workaround is to use date/time type instead of date.  It seems to work fine so far.

[Icey]

Hi @Anonymous ,

Glad to hear that. Please accept your solution above so that people who may have the same question can get the solution directly.

Best Regards,

Icey

[TheBeeGuy]

I've ran into the exact same problem today.  My dataflow has 20 entities and I have 7 that are suddenly missing when connecting to the dataflow from power bi desktop.

[Anonymous]

Thanks for the info.  It seems to be related with how the query is written.  Shortly after this, I will be posting 2 versions of the same query.  One works while the other doesn't.  I'm hoping someone can tell me what makes the other work even if they are basically querying the same table.  Although the one that doesn't work has a join.

Regards,

Ferdinand

[Anonymous]

Update: It seems like the updated Dataflow doesn't like some of the transformations from PBI desktop power query.  When I stripped all transformations from both queries, both worked.  Now I'm trying to check which transformation step is causing a query to disappear when connecting to the dataflow in PBI desktop.

[Anonymous]

The updated dataflow doesn't seem to like the step where I transformed 2 text columns to date.  Without that step, the query is visible when connecting to the dataflow in PBI desktop.  With that step, it disappears.  I'm going to continue to test what other steps could cause a query to disappear.  Please do the same and post here.  Thanks!

Regards,

Ferdinand

[Anonymous]

For my queries, it's definitely the change type to date that was causing the query not to be displayed when connecting to the dataflow in PBI desktop.  I had another query that wasn't showing up.  Scanned the steps and removed the change to type date step.  The query showed up.  I'm looking for another way now to convert columns to date type.  Let me know if someone has a workaround.