Scheduled Refresh Error In Dataflow but not PowerQuery

[PaulHet]

I've been trying to recreate some of my initial PowerQuery scripts into re-usable Dataflows on the PowerBI Service (using PowerQuery online rather than the desktop instance). In one of my scripts, I get the below error when trying to do scheduled refreshes or even on-demand refreshes. I do not get this error when initially writing the script within PowerQuery online, only when refreshing. I also do not get this error when it's written as part of my old Power Query and refreshed via a scheduled dataset refresh.... So it's something to do with writing it and refreshing within Dataflows specifically.

Error: Expression.Error: We couldn't convert to Logical. Request ID: 3c8a42bf-2c49-a85c-a102-6b8d857e633a Activity ID: fc541a08-7902-40c3-9818-e6498c883f0a

At the end of my code, I do convert a column to a Logical but I don't see why this is causing an issue...? 

See code below:

let

  Source = SharePoint.Files("https://COMPANYNAME.sharepoint.com/sites/PMO2/", [ApiVersion = 15]),

  #"Filtered rows 1" = Table.SelectRows(Source, each [Folder Path] = "https://COMPANYNAME.sharepoint.com/sites/PMO2/Shared Documents/General/Manager Knowledge Library/Org Charts/" and [Extension] = ".xlsx"),

  #"Filtered hidden files" = Table.SelectRows(#"Filtered rows 1", each [Attributes]?[Hidden]? <> true),

  #"Invoke custom function" = Table.AddColumn(#"Filtered hidden files", "Transform file", each #"Transform file"([Content])),

  #"Renamed columns" = Table.RenameColumns(#"Invoke custom function", {{"Name", "Source.Name"}}),

  #"Removed other columns" = Table.SelectColumns(#"Renamed columns", {"Source.Name", "Transform file"}),

  #"Expanded table column" = Table.ExpandTableColumn(#"Removed other columns", "Transform file", Table.ColumnNames(#"Transform file"(#"Sample file"))),

  #"Changed column type" = Table.TransformColumnTypes(#"Expanded table column", {{"Source.Name", type text}, {"Full Name", type text}, {"Job Title", type text}, {"Project/Department", type text}, {"Unique ID", type text}, {"Email address", type text}, {"Company Name (e.g. 9th Way Insignia/jlan/etc)", type text}, {"Town/City", type text}, {"State", type text}, {"Current LCAT (if applicable)", type text}, {"Veteran status (Y/N)", type text}, {"Security Clearance Level", type text}, {"Reports to (Full Name)#(lf)N.B. This must match the Full Name in Column A", type text}, {"Reports to (Title)#(lf)N.B. This must match the email address listed in Column B", type text}, {"Reports to (Project)", type text}, {"Reports to (Unique ID)", type text}, {"Data validation", type text}, {"Project Code", Int64.Type}}),

  #"Removed columns" = Table.RemoveColumns(#"Changed column type", {"Source.Name"}),

  #"Duplicated column" = Table.DuplicateColumn(#"Removed columns", "Veteran status (Y/N)", "Veteran status (Y/N) - Copy"),

  #"Filtered rows" = Table.SelectRows(#"Duplicated column", each [Unique ID] <> null and [Unique ID] <> ""),

  #"Replaced value" = Table.ReplaceValue(#"Filtered rows", "Y", "True", Replacer.ReplaceText, {"Veteran status (Y/N) - Copy"}),

  #"Replaced value 2" = Table.ReplaceValue(#"Replaced value", "N/A", null, Replacer.ReplaceValue, {"Veteran status (Y/N) - Copy"}),

  #"Replaced value 1" = Table.ReplaceValue(#"Replaced value 2", "N", "False", Replacer.ReplaceText, {"Veteran status (Y/N) - Copy"}),

  #"Changed column type 1" = Table.TransformColumnTypes(#"Replaced value 1", {{"Veteran status (Y/N) - Copy", type logical}}),

  #"Duplicated column 1" = Table.DuplicateColumn(#"Changed column type 1", "Unique ID", "UserCountTrue"),

  #"Changed column type 2" = Table.TransformColumnTypes(#"Duplicated column 1", {{"UserCountTrue", type logical}}),

  #"Replaced errors" = Table.ReplaceErrorValues(#"Changed column type 2", {{"UserCountTrue", true}})

in

  #"Replaced errors"

[v-yingjl]

I have created a similar table in SharePoint and a dataflow in Power BI Service, it works fine after refreshing it.

Whether it could be a temporary issue in Power BI Service or not, have you tried to refresh it in other time ranges to check it.

If it still has the same issue, you can consider providing more information about this issue like workspace type(Premium or other types), all error information(if it has detailed option) when it refreshes failed in Power BI Service etc.

Best Regards,
Community Support Team _ Yingjie Li

[PaulHet]

Hi, 

This is the full error message I get when trying to schedule refreshes, please see the same error with different scheduled refresh times. I am currently running in a Pro workspace (but have a premium trial license). I've been having this issue daily since 01/20

This is the most detailed error code I can find:

Please let me know if I can provide any additional details.

[v-yingjl]

Thanks for the information, have checked it internal based on the picture but it seems like not a type error but a token cache error.

Have you tried to create a new dataflow in different workspaces or create a dataflow without changing the type to logical to check whether it could be refreshed successfully becuase all queries in Power Query are about transforming data not other queires, or you can delete the query steps one by one from the query to monitor its refresh.

Best Regards,
Community Support Team _ Yingjie Li

[PaulHet]

Other queries work fine when not doing the transformation to logical. I figured out the problem though, basically I was getting that error when I had a column of data which generated [error] when changing the type. This occured when changing to Logical, or even Number (which is how I troubleshooted it). So the workaround is to make sure that all of my data is in the correct format before changing the data type.

In PowerQuery desktop, it can hold the errors in the data-type fine and then in the next step you can 'replace errors' to make them into the desired format etc, it appears this is a limitation of PowerQuery Online that it breaks as soon as it detects the errors, instead of holding them in logical memory until the end of the query transformation steps.

[v-yingjl]

I'm not certain whether it should be a limitation in dataflow and would investigate this issue continously because when I 'created' the error values in the table and use 'Replace errors' to change the value, it still could be refreshed successfully in my side.

Perhaps currently you can use your workaround, make sure that all of data is in the correct format before changing the data type, which could mitigate this issue temporarily and glad to hear that.

Best Regards,
Community Support Team _ Yingjie Li