Defaults(), or why are these two exact same columns not the same?

29 May, 2021

Has Power Apps ever given you an error so confusing, it makes you doubt your sanity?

Here is the error that I'm talking about.

The specified column 'A' does not exist. The column with the most similar name is 'A'

What?? I've gotten this error so many times, and it's so confusing because it doesn't give away any clues as to what is wrong. The two column names it references are spelled exactly the same. The column definitely exists in SharePoint. So what's going on?

If you already know what I'm talking about and you'd like to skip to the answer, you need to first set up a collection using Defaults() and pass in the Last() record of this collection into your Patch() or Collect().

Otherwise, first let me explain the situation.

The scenario

The scenario is one where I'm trying to Collect() or Patch() a record into a SharePoint List. "That seems normal," you might think. "Collect() as a function takes a record as an argument. How is that so odd?"

Two conditions need to happen to cause this error:

You're passing in a variable and not simply defining the record in the function call, AND
You have column names in SharePoint with special characters in it

Passing in a record variable

Imagine that you need your app to decide whether to Patch() or Collect(). If you're entering a new row of data into your register, you'll Collect(), and if you're editing an existing row, you'll Patch(). You're not using a Form object; you're using independent input controls. Either way, you're going to be passing in the same data.

So, to keep our code clean, you might try assigning your data into a record, and then passing in the data into your functions.

Set(
    recordData,
    {
        column: "data1",
        column2: "data2",
        ...
    }
);

If(
    isNew,
    Collect(
        sharePointList,
        recordData
    ),
    Patch(
        sharePointList,
        LookUp(sharePointList, ID = ThisID),
        recordData
    )
);

So far so good. The problem crops up if...

You have column names in SharePoint with special characters in it

As I discovered pretty quickly in my first few apps, this is a bad bad bad idea and should be disallowed. If you have special characters, every time you reference the column in Power Apps, you have to use single quotes, which is midly annoying. Less obvious are the errors that it introduces (this one in particular), which aren't so much of a headache when you know the solution, but a major problem if you don't.

Stick to this with naming your SharePoint columns and you should be fine:

Only use letters and underscores

Never do the following:

Begin a column name with a number
Have a space in the middle of the column name
Use special symbols except underscore

Error walkthrough

Let's break the rule and have our second called 2column. This is how the above code will now look:

Set(
    recordData,
    {
        column: "data1",
        '2column': "data2",
        ...
    }
);

If(
    isNew,
    Collect(
        sharePointList,
        recordData
    ),
    Patch(
        sharePointList,
        LookUp(sharePointList, ID = ThisID),
        recordData
    )
);

The variable recordData will now have a blue underline on it and the error tooltip will say:

The specified column '2column' does not exist. The column with the most similar name is '2column'

The solution

For some reason, Power Apps is not recognising the column that you're passing in as the same as the one in SharePoint, even though they are spelled exactly the same.

One way to get around this is to pull in exactly the columns from the SharePoint list.

The Defaults() function creates a table that is composed of the "default' values of your target data source. This will usually produce a table with one blank record in it.

You can now be sure that the column names in this table is exactly the same as Power Apps thinks is in the SharePoint List. If we do this, we rewrite the code above to...

// 🠗 new code 🠗
ClearCollect(
    colData,
    Defaults(sharePointList)
);

Collect(
    colData,
    {
        column: "data1",
        '2column': "data2",
        ...
    }
);
// 🠕 new code 🠕

Set(
    recordData,
    Last(colData)  // 🠔 changed line
);

If(
    isNew,
    Collect(2
        sharePointList,
        recordData
    ),
    Patch(
        sharePointList,
        LookUp(sharePointList, ID = ThisID),
        recordData
    )
);

The best solution however is to avoid situation in the first place but naming your SharePoint list columns with no special characters.

#powerapps