You’re very close — the issue is just where TrimEnds() is allowed to be used.
TrimEnds() works on text values, not on tables or entire collections.
So you can’t wrap it around Collect() or around the SQL result itself — it must be applied at the column level while you’re building the collection.
✅ Correct way to fix it
You need to trim the column inside the collection creation, using AddColumns or RenameColumns.
✅ Example
If your SQL result column is called BranchOffice, do this:
ClearCollect(
colBranches,
AddColumns(
SQLBranches,
"BranchOffice_Clean",
TrimEnds(BranchOffice)
)
);
Then set your dropdown to use:
✅ If you want to overwrite the original column name
You can do it like this:
ClearCollect(
colBranches,
RenameColumns(
AddColumns(
SQLBranches,
"BranchOffice",
TrimEnds(BranchOffice)
),
"BranchOffice",
"BranchOffice"
)
);
But normally the first approach is cleaner.
✅ Most common and simplest version
ClearCollect(
colBranches,
AddColumns(
YourSQLCollection,
"BranchOffice",
TrimEnds(BranchOffice)
)
);
Power Apps evaluates TrimEnds() row by row, which is exactly what you want.
❌ Why your attempts didn’t work
These don’t work because:
TrimEnds(Collect(...)) // ❌ TrimEnds expects text, not a table
TrimEnds(Result) // ❌ Result is a record/table
TrimEnds(SQLBranches) // ❌ table, not text
Power Fx functions are strongly typed — trimming must be applied to a text column, not to the dataset.
✅ Final dropdown setup
Dropdown.Items = colBranches
Dropdown.Value = "BranchOffice"
Now whether users enter:
"New York"
" New York"
"New York "
" New York "
they will all display identically.
✅ Bonus tip (recommended)
If duplicates are possible due to spaces, you can also normalize it:
ClearCollect(
colBranches,
Distinct(
AddColumns(
SQLBranches,
"BranchOfficeClean",
TrimEnds(BranchOffice)
),
BranchOfficeClean
)
);
This removes duplicates caused only by leading/trailing spaces.
✔ Bottom line
-
TrimEnds() must be applied inside AddColumns
-
It works per record, not on collections
-
This permanently cleans the dropdown display
Once you do this, you’ll never have to worry about leading or trailing spaces again 👍
