How to debug/troubleshoot chained dataflows?

[Kazu]

I have several workflows that are chained, using the "staging dataflow" and "transformation dataflow" approach.

One of my statging dataflows started to fail recently. Here is a log file (screenshot). I thought the problem is with my Transformation Dataflow - 1, highlighted in yellow. However, when I manually refresh the transformation flow, it completes without any errors. If I manually refresh the staging flow, it always failed.

I opened the transformation flow and checked each query, sometimes click Refresh button within the query designer. I could not find any errors. Then I turned off the "Enable load" fot the linked sources from the staging flow, so that there will be no chaned refresh. The refresh of the staging flow completed just fine. (Of course there was no chained refresh of the transformation flow 1)

So I am completely lost? What causes the failure and how can I troubleshoot?

[Anonymous]

Hi @Kazu ,

From your description, it appears that the failure occurs when the staging data stream attempts to chain refresh the converted data stream. Here are a few troubleshooting steps you can take to hopefully resolve this issue:

1. Ensure that all dependencies between the staging data stream and the conversion data stream are properly configured. This includes checking that the output of the staging data stream correctly matches the expected input of the conversion data stream.
2. Sometimes data flow failures (especially in chaining scenarios) can be caused by permission issues. Ensure that the data stream has the correct permissions to access all necessary resources. This includes checking permissions on the source data, any intermediate storage, and the destination.

If the problem persists after following these steps, it would be helpful to gather more specific information:

• What is the exact error message received when a staging data stream fails?
• Were there any recent changes to the configuration of the staging or conversion stream before the problem started?
• Are there specific transitions or steps in the conversion stream that depend on the output of the staging stream?

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kazu]

@Anonymous , thanks for the advice. 

What confuses me the most is that when the staring flow and the transformation flow are refreshed sapearetely, both complete just fine, whether on demand or scheduled.

In the illustrative diagram below, the transformation flow had several tables linked from the staging flow, and then had following queries that do merge, append, group etc. etc. The greyed out tables/queries were disabled with "Enable Load", so no chained/linked refresh occured. Two flows required refresh one by one. This worked perfectly fine.

However, recently I have some slow refresh issue and learned in principle I should turn on the "Enable Load" for better performance, so turned it on. Now, one of the queries in the Transformation flow (red below) started to raise an error when it refresh was triggered as a chained/linked part of a refresh of the stating flow. 

The error message is like below

Error: Expression.Error: We cannot convert the value null to type Logical. . RootActivityId = <GUID>.Param1 = Expression.Error: We cannot convert the value null to type Logical. Request ID: <GUID>.

There hasn't been changes to the flow for quite a while, except turning on the "Enable Load" options.

Are there specific transitions or steps in the conversion stream that depend on the output of the staging stream?

That is exactly what I wish I knew!! I have no clue other than the error messages in the log file. It does not tell which recored caused the error, or even which step. I don't think there are any steps in my flow where I convert null to logical. And again, the refresh completes just fine if not chained. WHY?

[Anonymous]

Look for a column in one of the Power Query 'Steps' where it changes the Data Type to 'Logical'. That column has a null instead of a 1 or 2 OR Yes or No. That transformation is failing.

When you view the PowerQuery query in a Dataflow, it does not ingest all the data in the query, only say 1000 rows to be able to display to you in the design mode. You can try to load ALL the data in the design step to ind the error then.

[Kazu]

Thank you for your advice.

As far as I tested using Power BI Desktop, the conversion of column data type to Logical would not raise this error. Here is a very simple test. Null is converted to logical without any errors.

I actually had the same errors before and was able to reproduce the same error message. This error happened when I defined a custom function that took a parameter of logical type. If I changed the parameter to any type, it was resolved. I do have a few custom functons but none of them should have logical type parameters. Will double check but I don't think this is the reason this time.

In the query designer of dataflow there is no such option as "Refresh all".

And above all, as I said if I disable the "Enable load" and manually refresh the staging flow and transformation flow one by one, they just completes without any errors. Why???