If you examine the refresh details for a Power Query gen2 dataflow, you’ll notice that the activity names which are displayed are identical to the query names in your dataflow except that each has a “_WriteToDataDestination” suffix appended to it. What’s going on with this?
Let’s see what we can uncover! Time for some sleuthing….
Continue readingTable.Buffer and List.Buffer buffer data—but how do they work? How deeply do they buffer? How do they handle errors? And, for that matter, when do they populate?
In a nutshell: Table.Buffer creates a stable copy* of a table’s rows. These rows are fetched only once from the source, regardless of how many times they are read from the buffer. Each time the buffer is accessed, the same* rows are returned in the same order. With List.Buffer, the behavior is identical, except it is list items that are buffered instead of table rows. (*But this may not mean what you think, so keep reading.)
The details have some nuances to them. Let’s explore them, as well as what happens when errors are encountered.
Continue readingOnce upon a time, I was bit by a time column which straightforward equality-comparison DAX wouldn’t filter as expected. From this saga, I learned a couple important lessons about Power BI’s data types that are not intuitively obvious from the user interface.
Imagine some simple DAX like the below. When evaluated, the result I’d receive back was blank—even though rows with the matching time could be plainly seen in Power BI’s table view.
CALCULATE (
COUNTROWS ( Data ),
Data[Timestamp] = TIME (12, 30, 0)
)
Why this strange behavior? Turns out, Power BI’s UI was not displaying the full data values in the column. When viewed in the UI’s table view, it looked like matching values existed in the column, but actually they didn’t. Comparisons were being performed correctly, just not against the time-only values I thought were there.
Continue readingSeems simple enough: The table, produced by your Power BI gen2 dataflow, contains a time column. You want to output this table to OneLake, then have Power BI read from it using a DirectLake-powered semantic model.
Sounds easy, but not so fast! When you try to set up the dataflow’s output mappings, columns of type time are not supported for OneLake destinations. Ouch!
So, what do you do?
Try leveraging the fact that Power BI’s time values are, behind the scenes, actually datetime values. For proper “type time” behavior, Power BI expects that the date part of each datetime value is set to Power BI’s epoch date (the date it considers day 0, which is 1899-12-30) and it also needs to know that the column is expected to have time behaviors.
Continue readingDuring 2023, the Power Query M Language Specification received four non-trivial revisions (beyond typo fixes, formatting tweaks, and the such). One change documented a newish language feature; the remaining each brought clarification to ambiguous points or corrected cases where the specification did not align with actual mashup engine behavior.
Continue readingPower Query’s simple scalar values—like date, number and logical—can easily be converted to strings. Each has a corresponding type-specific “ToText” method (like Date.ToText or Number.ToText). The generic Text.From can also be used.
But what if you want to render a table, list or record textually? There is no built-in way to convert values of these types directly to text.
However, you can convert them to JSON…and then render that JSON as text!
(input as any) as nullable text =>
if input = null
then null
else Text.FromBinary(Json.FromValue(input))
Handy to render out a complex, nested structure so that you can see all of it at once!
Continue readingStaging data is common in the world of Microsoft Power BI dataflows. Computed entities, linked entities and Microsoft Fabric’s “enable staging” options all result in intermediate output being staged to disk and/or a database.
Does this staging affect privacy levels? Yes! It can change which privacy level is being applied.
Continue readingIf you’ve built a Power BI dataflow that combines between sources, most likely you’ve been stopped by a prompt asking if you want to “continue” because there is a risk that data could be revealed from one source to another.
![Screenshot of prompt:
The evaluation was canceled because combining data from multiple sources may reveal data from one source to another. Click Continue if the possibility of revealing data is okay.
[Continue button]](https://bengribaudo.com/wp-content/uploads/2023/07/EvaluationCanceled-ContinueToCombine.png)
The prompt’s wording makes it sound like you must choose “continue” in order to be able to use dataflows to output data derived from more than one data source—but is continuing truly mandatory?
The seeming necessity of enabling this option is reinforced by how the corresponding setting appears in the dataflow’s Options dialog. Clicking “continue” in the above prompt sets this checkbox. Its wording implies that it must be checked in order for Power Query to be able to combine between multiple data sources: If you don’t check it, you won’t be allowed to combine data from more than one source—or so it (incorrectly) seems.
Thankfully, in most cases, you do no need to enable this option in order to combine between sources.
Continue readingPower Query’s RowExpression.From/ItemExpression.From function (both names reference the same underlying function) provides a way to learn about what a single-parameter function does by outputting an abstract syntax tree (AST) describing it.
Why might you want to use a programmatic structure like an AST to analyze the logic of a function’s body instead of simply invoking the function?
Well, one reason may be that you are implementing query folding in a custom connector. You might want to translate the filter predicate function passed to Table.SelectRows, or the generator function passed to Table.AddColumn, into the upstream data source’s native query/language. In either case, you don’t want to invoke the passed-in function; instead, you want to understand its behavior so that you can factor it in as you build an equivalent native request/query. RowExpression.From/ItemExpression.From is tailored for this purpose.
Unfortunately, this function is little documented—but it is time for that to change!
(Note: For simplicity, the below will refer to this function by the name RowExpression.From. However, ItemExpression.From is an equally valid way to reference the function.)
The on-premises data gateway isn’t just for enabling Power BI Service cloud-hosted components to pull data from private local network resources.
True, Microsoft’s short description of the gateway says that it is “a bridge” which “provides quick and secure data transfer between on-premise data, which is data that isn’t in the cloud, and several Microsoft cloud services.” However, beyond that description, there are at least three additional key uses for this gateway which are of particular pertinence in the Power BI world.
Continue reading