Dataflows out of sync in Pipeline

[MNorstedt]

Starting about a month ago (around 2024-10-18) I noticed that all my Gen1 dataflows are showing out of sync between all workspaces (dev, test, prod) in our pipeline. Re-elevating doesn't resolve the issue, and digging down it looks like the source has line feeds showing at the end of the connection string. DEV always shows the line feed in relation to TEST and TEST always shows the line feed in relation to PROD. The Advanced query does not indicate any line feeds so it looks like it is something added by the service? 

The "pbi:mashup": "document" is always the sync culprit and the only difference is the source dataflow always has "Source;\r\n" at the end where the detination shows "Source;"

Has anyone experienced this or have any ideas?

[Poojara_D12]

Hi @MNorstedt 

It sounds like you're encountering an issue where an extra line feed (\r\n) is being appended to the end of your connection string in Gen1 dataflows across multiple workspaces (DEV, TEST, PROD) within your Power BI environment. This discrepancy is causing your dataflows to be flagged as "out of sync," likely because of a difference in how Power BI service handles line endings in these strings. Here are a few troubleshooting steps and potential workarounds that might help:

1. Check for Manual Fixes in Query M Language

• Even though you mentioned that the advanced query does not indicate any line feeds, it may still be worth revisiting the query code in the Power Query Editor across all environments to ensure that there aren’t any hidden or special characters.
• If feasible, remove any \r\n or \n at the end of the Source string in the M code and save it again, then re-deploy. This might ensure consistency across environments.

2. Inspect Dataflow JSON Directly (if possible)

• If you have access to export and inspect the JSON definition of the dataflows (under the pbi:mashup field), look for any differences in line endings between environments. This may give you a clearer view if the problem originates in how Power BI interprets or stores the line endings.
• Note that the Power BI API (especially in Gen1) can sometimes treat line endings inconsistently, depending on workspace or version, causing minor syntax mismatches.

3. Force Text Cleanup in Power Query

• In the Power Query Editor, you could try adding a cleanup step to standardize the connection string across environments explicitly. This can be done by adding a final step that removes or replaces unwanted line feeds:

Text.Replace(Source, "#(cr)#(lf)", "")

This step would strip any \r\n from the connection string before using it, which may reduce the sync issues.

• 4. Dataflow Refresh and Re-deployment

  • If possible, re-create the dataflows in each workspace from scratch, ensuring no trailing line feeds are introduced during setup. After doing this, re-deploy in a controlled sequence, first in DEV, followed by TEST and PROD, verifying sync consistency at each stage.

  5. Consider Gen2 Migration

  • If feasible, consider migrating to Power BI Dataflows Gen2, which is designed to improve on the limitations of Gen1. Gen2 may handle metadata and M code more consistently across workspaces, potentially preventing this kind of issue.
  • Gen2 also provides more robust monitoring and error tracking, which could be beneficial if these types of inconsistencies continue to arise.

  6. Raise a Support Ticket with Microsoft

  • Since this could be an internal bug with how Power BI handles or stores line endings, it may be worth reaching out to Microsoft support. Other users have reported similar issues with seemingly minor differences in M code syntax causing sync failures. By raising a ticket, Microsoft may be able to provide a hotfix or workaround specifically for Gen1 dataflows.
•  
• This combination of cleanup steps, checking the JSON directly, and possibly upgrading or consulting with Microsoft should help you find the root cause or, at the very least, mitigate the issue until there’s a permanent solution. Let me know if any of these approaches help or if additional diagnostics are needed!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[MNorstedt]

Poking around I think I found the issue... It's actually the Pipeline parameters. As soon as I delete the pipeline rule and republish they show in sync. Reapplying the parameter rule shows out of sync again. Looks like I will be opening a ticket to see what the techs say since this is a change in experience.

Updating to Gen 2 isn't going to work for me because in the default config it looks like the refresh takes 4X longer then the same Gen 1 flow, and turning off staging still has the Gen 2 running twice as long as my Gen 1. This isn't going to work given the number of flows we have in production unfortunately.

Thank you for the advice and things to look at.

[MNorstedt]

Thank you for the advice. I will try these and see if one of them resolves the issue.

[rajendraongole1]

Hi @MNorstedt -  you're experiencing an issue with Gen1 dataflows where minor formatting differences, such as extra line feeds in the connection string, are causing sync discrepancies across your environments (DEV, TEST, and PROD) in Power BI. 

Verify Power BI Service Updates: Starting around October 2024, Power BI may have implemented service-side updates that affect how dataflows are handled or saved. Line feed discrepancies can sometimes result from updates to parsing logic or internal formatting applied during dataflow refreshes. You may want to check the Power BI monthly updates or contact Microsoft support to see if this behavior is a known issue or if it’s on their radar.

Check for Hidden Characters in the Connection String: Although the Advanced Editor doesn’t show line feeds, hidden characters can sometimes appear when copying and pasting connection strings across different environments. You could try copying the source connection string to a plain text editor that reveals hidden characters (e.g., Notepad++ or VS Code) to ensure there are no invisible carriage return/line feed symbols. Removing these characters before pasting may help.

Power BI REST APIs for embedded analytics and automation - Power BI REST API | Microsoft Learn

Manually Remove Extra Line Feeds: If you can access the dataflow definitions in JSON format, try exporting the dataflow and manually removing any instances of \r\n from the connection strings in a text editor. Re-import the adjusted dataflow to each environment to see if this resolves the sync issues. However, this is a workaround and may require revalidation each time the dataflow is updated.

Use Power BI REST API for Consistent Deployment: If re-elevating within Power BI isn't resolving the issue, consider using the Power BI REST API to automate dataflow deployments. This could help maintain consistency across environments by scripting the deployment process, potentially bypassing the line feed discrepancy issue that appears when syncing through the UI.

Since this appears to be an issue possibly introduced by the service itself, opening a support ticket with Microsoft could help. They may be able to provide guidance, especially if other users are experiencing similar sync issues. Sometimes, Microsoft can apply a backend fix if the issue is related to how the service processes and saves connection strings.