Power 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
Handy to render out a complex, nested structure so that you can see all of it at once!
Staging 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.
If 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.
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.
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.
Lazy evaluation, streaming and immutability are key Power Query concepts which must be understood to truly grasp how Power Query “thinks.” Want to test your understanding in these foundational areas—and try to grow it—by tackling an assignment?!
Code up a single row table containing the following columns:
A column of hard-coded data.
A couple columns whose data is fetched from an API.
A couple columns whose data is fetched from another API.
Neither API is called if the table’s rows are simply counted.
Only the first API is called if the columns containing data from the second API are removed from the table, and vice versa.
Each API is called at most once, even when multiple columns that contain data from that API are output.
Using any standard library table functions to build the table (i.e. no “Table.*” functions may be used).
If you work in the advanced realm of ascribing types, there are a couple interesting behaviors to be aware of related to table column renames (including a bug!).
Positional, Not Name-Based
Imagine you want to set column type claims on a table, so you create a query that uses Value.ReplaceType to ascribe an appropriate new table type.
// in real life, comes from a database
Data = #table(
Source = BaseDataSet,
Result = Value.ReplaceType(Source, type table [Amount = Currency.Type, TransactionID = Int64.Type])
So far, so good.
Later on, someone decides that the ID column should be moved to be leftmost, so they reorder columns by editing BaseDataSet. However, they don’t touch your MyNicerTable query with its Value.ReplaceType code. Look closely at what that expression now outputs:
The column that contains transaction IDs is now named “Amount” and typed Currency.Type. Similarly, “Amount” values now show up under the column name “TransactionID” which is typed as whole number. Ouch!
Numbers and dates are formatted differently in different parts of the world. How are these cultural differences handled in the realm of Power Query? Turns out, arguably, there can be not just one—but two—sets of rules in play.
In the M language, numbers and date/time-based values are natively stored in culture-agnostic formats. It doesn’t matter what part of the world you’re in or how it formats values, when #date(2023, 1, 23) is evaluated, Power Query understands that the referenced year is 2023, the month is 1 and the day is the 23rd—and it maintains this understanding throughout the value’s lifetime. Similar holds true with the other date/time types, as well as with numbers.
On the other hand, when converting values of these types to or from text, culture does come into play—but which culture?
// 123,456.78 (if the culture is en-US)
// 123.456,78 (if the culture is es-ES)
// 123 456,78 (if the culture is se-SE)
During 2022, the Power QueryM Language Specification received seven substantiative revisions (beyond typo fixes, formatting tweaks, and the such). Each brought clarification to ambiguous points or corrected cases where the specification did not align with actual mashup engine behavior. None of the revisions added new language functionality or otherwise resulted in the mashup engine changing.