Posts

Showing posts from August, 2025

Passing Pipeline Trigger Time to Data Flows in Azure Data Factory: Use Strings, Not Timestamps!

Image
When working with Azure Data Factory (ADF) and the Dataverse connector , passing the pipeline trigger time into a Data Flow can be trickier than expected. The Scenario You want to pass the pipeline’s trigger time—using the @pipeline().TriggerTime system variable—into a Data Flow. This is often needed for auditing, filtering, or other time-based logic. The catch? You’re using Dataverse , which communicates over the Web API and handles datetime values as strings . The Common Mistake In Azure Data Factory, you might instinctively define the Data Flow parameter as a timestamp or date type. But ADF doesn’t have a dedicated datetime type—only date and timestamp . So you choose one of those, thinking it aligns with your goal. Then you hit an error. And to make matters worse, the error message doesn’t clearly explain the real issue—it can be vague or misleading, which only adds to the confusion. This tripped me up for a while, as I assumed the problem was elsewhere. The Solution...

Power Query: Driftless Merges using Table.Buffer

Image
What happened Recently I was working on data where I needed to pick one best row per group, then merge that result with a lookup table. Here’s the head-scratcher I hit: the pick looked right in preview, but after the merge some groups showed different rows, like the merge had used the pre-pick data. What was happening is that Power Query re-evaluated and re-ordered things during the merge, which changed which row got selected. The fix was to freeze the picked result with Table.Buffer right after the pick so the merge used exactly those rows. I also made the lookup one row per key to avoid duplicate expands. After that, everything stayed stable on refresh. Why and how Table.Buffer works Why the drift happens Power Query is lazy. It does not materialize intermediate steps until needed. A Merge can push work back to the source (folding). That re-evaluation can change row order. If your “pick one” depends on order, the selected row can change during the Merge. What Table.Buffer...