Dataverse Web API Tip #13: Templates for New Entities

When creating a related record in Microsoft Dynamics/Power Apps, ever notice how the “quick create” form for the new entity may be displayed with some fields prepopulated?

Screenshot of Power Apps contact quick create form

For example, on an account, go to related contacts and click “New Contact.” By default, the create contact form that pops up shows account, phone and address pre-filled in with data from the account. The user can leave these values untouched or edit them, at their preference, before clicking “Save” to actually create the new entity record.

If you’re working programmatically—say using the Dataverse Web API—and would like to prepopulate fields like this from another entity, how would you go about pulling this off?

You could maintain your own mappings, like “When creating a contact for an account, set fields x, y and z on the new contact to the corresponding values from the related account.” While technically valid, this requires you to own the responsibility of configuring and storing these mappings. What if, instead, you could simply use the exact same template that the Dynamics/Power Apps UI uses?

Continue reading

Always Identical Outputs? Switch Statement vs. Switch Expression

, ,

Are these two statements identical in what they return?

public object Switcher(bool flag) 
{
  switch (flag) {
    case true:
      return 1;
    case false:
     return 10.5;
  }
}
public object Switcher(bool flag) 
{
  return flag switch {
    true => 1,
    false => 10.5
  };
}

No.

TestResult –
Switch Statement
Result –
Switch Expression
Assert.Equal(1, x.Switcher(true));PassFail – Error Message:
Assert.Equal() Failure
Expected: 1 (System.Int32)
Actual: 1 (System.Double)
Assert.Equal(10.5, x.Switcher(false));PassPass

Scratching your head trying to find the difference? The logic in both looks identical. Arguably, it is.

Continue reading

Dataverse: Same data everywhere? Dynamics/Power Apps UI + Web Services vs. TDS Endpoint + Power BI/Power Query + Azure Synapse Link

, ,

If you try accessing the same data using the various mechanisms listed below will you always receive back identical values?

It depends.

Continue reading

M Language Proposal: Cleaning Up Function Chains with the Pipeline Operator

, , ,

Sometimes, a chain of M function calls reads as a dense blob of code, yet refactoring to the clearer structure of a let statement is an overkill. Let’s look at an alternative, a new operator to consider for inclusion in the M language.

The Problem

You’d like to add a column to your customers table that holds the average amount of the given customer’s three largest completed orders. The needed data is already available in the table, thanks to a nested orders table. All that’s needed is for you to define logic that uses this data to calculate the desired average.

To pull this off, your new column’s logic needs to:

  1. Filter the nested orders table to Status = "Completed".
  2. Sort by Total, descending.
  3. Take the top 3 results.
  4. Average their Totals.
Continue reading

Power Query M Primer (Part 22): Identifier Scope II – Controlling the Global Environment, Closures

, , , ,

As we learned last time, normally, M code is evaluated in a global identifier resolution scope consisting of all shared members + the standard library. Also, normally, we can’t inject additional identifiers into this global environment. Normally isn’t always. Today, we learn about the exception: where both of these normalities do not apply.

That’s not all: Did you know that M has a mechanism for remembering how to access variables that later go out of scope? Closures open up powerful options, particularly when generating functions…and even enable building an object-like programmatic construct that maintains internal private state and is interacted with through a public interface (kind-of, sort-of somewhat like an object from object-oriented programming!).

Continue reading

M Mysteries: The Mysterious Type Action—An M-Internal Means to Write Data Modifications to External Systems

, ,

Power Query is great for reading, combining and computing data, but it’s not meant for writing data modifications—like inserts, updates or deletes—back to the source. Correct?

Yes and no.

What?!

If you are a non-internal user, then yes, Power Query is intended to be read only: it does not expose functionality meant for inserting, updating or deleting data on remote systems. But this doesn’t mean Power Query lacks this capability: to the contrary, it has hidden, internal support for performing data modifications!

Continue reading

Dynamic, Lazy Records

, ,

In the below record, when is Amount‘s value calculated?

[
  FieldA = …,
  Amount = ExpensiveToCompute("some", "arguments"),
  …
]

Only if needed. Why? Power Query’s record field values are lazily evaluated. A field’s expression is only evaluated if its value is needed. If it’s not, the cost of computing the value isn’t expended. Nice!

Let’s say, instead, you’d like to dynamically add Amount to an existing record. Is something like the following effectively equivalent to the above?

let
  SomeExistingRecord = [
    FieldA = …,
    …
  ]
in
  Record.AddField(
    SomeExistingRecord, 
    "Amount", 
    ExpensiveToCompute("some", "arguments")
  )

No! Whoa! Amount‘s laziness went good bye! Above, ExpensiveToCompute("some", "arguments") is executed whether or not Amount‘s value is ever needed.

Continue reading

Equals Is Not Always Equivalent: Power Query Joins vs. SQL Joins

, , , ,

Take the following M expression:

Table.Join(A, "ID", B, "ID", JoinKind.Left)

Does it behave like the below SQL (which is how a join between two tables on column ID would typically be coded in the database world)?

FROM A
  LEFT JOIN B ON A.ID = B.ID

Perhaps surprisingly, no—at least, not when the simple, innocent null is involved.

Continue reading

Equals Is Not Always Equivalent: When Query Folding Does Not Produce Identical Results

, , , ,

Query folding is supposed to be transparent, as far as results go. Whether or not a Power Query expression is folded should have no effect on the data returned. You should receive back identical results either way. At least, that’s the theory.

Unfortunately, this is not always the case!

The fact that query folding sometimes changes the results that are returned can bite unexpectantly. You have an M expression that produces exactly what you want. Then you make what should be an innocuous edit, but behind the scenes the change affects whether or how the query is folded. The results you now receive back are no longer what you expect, and puzzlingly the divergence seems to have no obvious relation to your edit. Or, maybe you didn’t edit anything at all: instead, a Power Query update changed the foldability of your query without you touching it. You made no changes, yet the data returned is now different.

Continue reading

M Mysteries: SQL -> M (SqlExpression.ToExpression)

, , ,

Did you know that Power Query can convert (some) SQL into M code?!

No, I didn’t mean that the other way around. It’s true that Power Query can query fold M into SQL (M -> SQL), but that’s not what I’m referring to here. M also has some capability to take a SQL statement and translate it to Power Query code (SQL -> M).

(Disclaimer: As fun as SQL -> M may look, this technique is best not relied upon in production, for reasons that will be explained. However, exploring this functionality—especially the possible why behind its presence—may have educational benefits.)

Without further ado, let’s convert SQL to M:

let
  ReferenceTables = [Department = Department, Company = Company],
  SqlQuery = "
	SELECT d.dept, c.name
	FROM Department d
	  LEFT JOIN Company c ON d.id = c.id
	",
  TranslatedToM = SqlExpression.ToExpression(SqlQuery, ReferenceTables)
in
  TranslatedToM
Continue reading