One button, fifty rows: bulk write-back from Power BI to Fabric SQL using a UDF | Part-1

Most writeback examples you'll find handle one record at a time. This one takes whatever the user has selected in the slicer — one hotel or fifty — passes the whole selection to a User Data Function as a single call, and writes it all in one transaction.

Every reporting team eventually gets the same request. Someone looks at a table in a Power BI report, spots a value that is wrong, and asks why they have to email the data team to fix it. In our case it was hotel code setup: mapping each hotel's code types to a Major Group and a Minor Group. The mappings changed often enough to be annoying and rarely enough that nobody wanted to build a whole application for it.

Translytical task flows finally made this practical. This post walks through the setup I actually built and shipped.

The stack is Fabric SQL database → User Data Function → a button in a Power BI report.

What it needed to do

Three requirements shaped everything else.

Edit one hotel or fifty in a single click. Code type mappings are usually rolled out across a brand or a region, not one property at a time. If the user has to click through hotels one by one, they will go back to emailing spreadsheets.

Record who changed what, at column level. "Someone changed the Major Group last month" is not an audit trail. I wanted old value, new value, the person's actual name, and a timestamp, for each column that moved.

Fail loudly and readably. The people using this are finance and operations users, not developers. An error has to tell them what to do next, not surface a SQL exception.

1. The SQL database

Everything starts with the schema. The full script is at the bottom of this post, but three decisions in it are worth explaining because they are the ones that determine whether bulk editing works at all.

The primary key

The original table used CodeType as the primary key. That works fine for one hotel and falls apart immediately for many, because five hotels sharing the code type FBFOOD need five separate rows.

So the table gets a surrogate identity column as the PK, and the real business rule moves into a unique constraint:

CONSTRAINT PK_HotelCodeSetup PRIMARY KEY CLUSTERED (HotelCodeSetupId), CONSTRAINT UQ_HotelCodeSetup_Hotel_Code UNIQUE (HotelCode, CodeType)

The unique constraint is what actually stops duplicates. The surrogate ID exists so the audit table has something stable to point at.

The audit table stores one row per changed column

Not one row per changed record. If a user updates Major Group and leaves Minor Group alone, I want exactly one audit row, saying which column moved and from what to what.

CREATE TABLE audit.HotelCodeSetupAudit ( AuditId BIGINT IDENTITY(1,1) NOT NULL, BatchId UNIQUEIDENTIFIER NOT NULL, InvocationId VARCHAR(100) NULL, HotelCodeSetupId INT NULL, HotelCode VARCHAR(10) NOT NULL, CodeType VARCHAR(20) NOT NULL, ActionType VARCHAR(10) NOT NULL, ColumnName VARCHAR(50) NOT NULL, OldValue NVARCHAR(200) NULL, NewValue NVARCHAR(200) NULL, ChangedBy NVARCHAR(200) NOT NULL, ChangedByOid VARCHAR(50) NULL, ChangedDateUtc DATETIME2(3) NOT NULL, SourceApplication VARCHAR(50) NULL );

BatchId is the piece that makes this usable. One button click produces one BatchId, however many hotels it touched. That gives you a natural unit for "show me everything that click did", and later, for rollback.

Why the audit is not a trigger

This is the one I would have got wrong if I hadn't tested it as a second user.

A trigger runs under the identity of the database connection. In this architecture that connection belongs to the User Data Function, so every single change would be attributed to the same account. The audit would be technically complete and practically worthless.

The user identity has to come from the function's call context and be passed in as a parameter, which means the audit writes have to happen inside the stored procedure, in the same transaction as the change itself.

The upsert

MERGE handles single and multi-hotel through one code path. The hotel list arrives as a comma-delimited string and gets split into a table variable, so one hotel is just a list of length one and there is no separate branch to maintain.

MERGE dbo.HotelCodeSetup WITH (HOLDLOCK) AS tgt USING (SELECT HotelCode FROM @Hotels) AS src ON tgt.HotelCode = src.HotelCode AND tgt.CodeType = @CodeType WHEN MATCHED AND ( ISNULL(tgt.MajorGroup, '') <> ISNULL(COALESCE(@MajorGroup, tgt.MajorGroup), '') OR ISNULL(tgt.MinorGroup, '') <> ISNULL(COALESCE(@MinorGroup, tgt.MinorGroup), '') OR tgt.IsActive = 0 ) THEN UPDATE SET

Two details in there that matter more than they look:

COALESCE(@MajorGroup, tgt.MajorGroup) is how "leave this column alone" works. A blank text slicer becomes NULL, and NULL means keep the existing value rather than wipe it. Without this, a user who only wants to change Minor Group would silently blank out Major Group.

The WHEN MATCHED AND (...) guard means rows that already hold the target values are not touched at all. No pointless ModifiedDateUtc churn, and no audit noise.

The OUTPUT clause writes $action plus old and new values into a table variable, which then gets unpivoted with CROSS APPLY into the column-level audit rows. That unpivot is where the "skip columns that didn't change" filter lives.

Deletes are soft. usp_DeactivateHotelCodes sets IsActive = 0 and audits it, so the audit trail always resolves to a row that still exists.

Run the smoke tests before Power BI is anywhere near this

The bottom of the script has direct EXEC calls for both single and multi-hotel. Run them. If the procedure works in the query editor, then every problem you hit later is a Power BI or UDF problem, and you have halved your search space.

EXEC dbo.usp_UpsertHotelCodeGroups @HotelCodes = 'HTL001,HTL002,HTL003', @CodeType = 'RMREV', @MajorGroup = 'Rooms Division', @MinorGroup = NULL, @ChangedBy = '[email protected]';

2. The User Data Function

Create a User Data Functions item in the same workspace. Under Manage connections, add the SQL database, and note the alias Fabric generates. That alias goes into the decorator.

import fabric.functions as fn udf = fn.UserDataFunctions() @udf.connection(argName="writebackDb", alias="Writeback") @udf.context(argName="callContext") @udf.function() def apply_hotel_code_groups( writebackDb: fn.FabricSqlConnection, callContext: fn.UserDataFunctionContext, hotelCodes: str, codeType: str, majorGroup: str = "", minorGroup: str = "", ) -> str:

Three platform rules that are not optional:

• Parameter names must be camelCase. No underscores. hotel_codes will not work.
• The function must return str if you want it to show up in the Power BI button picker. A function returning a dict simply won't appear in the list, with no explanation.
• import fabric.functions as fn and the udf = fn.UserDataFunctions() line are both required.

If the editor complains about the stacked @udf.connection and @udf.context decorators, swap their order. Both sit above @udf.function().

Getting the real user

def _identity(callContext) -> tuple: try: user = callContext.executing_user or {} return (user.get("PreferredUsername") or "unknown", user.get("Oid")) except Exception: return ("unknown", None)

PreferredUsername is the readable one for the report. Oid is the Entra object ID, which is immutable and survives people changing their name or email. Store both.

Validation lives in Python

The stored procedure validates too, as a backstop, but the messages users actually see come from UserThrownError:

if not codes: raise fn.UserThrownError( "No hotel selected. Use the hotel slicer to pick one or more hotels first.", {"hotelCodes": hotelCodes}, ) if len(codes) > MAX_HOTELS_PER_CALL: raise fn.UserThrownError( f"{len(codes)} hotels selected, which is over the {MAX_HOTELS_PER_CALL} limit. " "Narrow the selection and apply in smaller batches.", {"selectedCount": len(codes)}, )

MAX_HOTELS_PER_CALL is set to 250. This is a guard rail, not a performance tuning knob. Power BI hands the function whatever the slicer selection contains, and large payloads run into UDF request size and execution timeout limits. A bulk edit that times out halfway is exactly what the transaction in the procedure is there to protect you from, but it is better not to get there.

The return string is your UI

Since the function has to return a string anyway, make it a useful one. The procedure returns a result set with counts, and the function turns that into something a user can read in the toast notification:

return (f"'{code_type}' across {requested} hotel(s): " f"{detail}. {audit_rows} audit row(s) written. Batch {str(batch_id)[:8]}.")

So the user sees something like "'RMREV' across 12 hotel(s): 9 updated, 3 already matched. 9 audit row(s) written. Batch a3f2c101." That last part is genuinely useful when someone reports a problem, because you can look up the exact batch.

One thing that will bite you when reading the procedure's output: SET NOCOUNT ON helps, but you can still land on a resultless set before the SELECT. Skip forward until there is something to read:

def _first_result_row(cursor): while cursor.description is None: if not cursor.nextset(): return None return cursor.fetchone()

Publish, then test in the portal's Run pane before touching Power BI.

3. The semantic model

Connect Power BI Desktop to the SQL database in DirectQuery.

Point at the SQL database endpoint, not the SQL analytics endpoint. This one cost me an hour. The analytics endpoint mirrors into OneLake on a delay, so when the button refreshes the report the write has not landed yet. The user clicks Apply, the table doesn't change, and they conclude the button is broken. It isn't. You're just reading a copy.

Bring in dbo.HotelCodeSetup and audit.vw_HotelCodeSetupHistory.

4. DAX

This measure is what makes multi-hotel editing work. It collapses the slicer selection into the comma-delimited list the function expects:

Selected Hotel Codes = IF ( ISFILTERED ( HotelCodeSetup[HotelCode] ), CONCATENATEX ( VALUES ( HotelCodeSetup[HotelCode] ), HotelCodeSetup[HotelCode], "," ), BLANK () )

Do not skip the ISFILTERED guard. Without it, an empty slicer returns every hotel in the model. One stray click and you have rewritten the entire estate, correctly, transactionally, and with a beautiful audit trail of the disaster. Returning BLANK() instead makes the function reject the call with a readable message.

Selected Hotel Count = IF ( ISFILTERED ( HotelCodeSetup[HotelCode] ), COUNTROWS ( VALUES ( HotelCodeSetup[HotelCode] ) ), 0 ) Selected Code Type = SELECTEDVALUE ( HotelCodeSetup[CodeType] )

SELECTEDVALUE returns blank when zero or several code types are selected, so ambiguous input gets rejected rather than quietly applied to the wrong one.

And a live button label, because bulk edits deserve a preview of the blast radius:

Apply Button Label = VAR _n = [Selected Hotel Count] VAR _type = [Selected Code Type] RETURN SWITCH ( TRUE (), _n = 0, "Select at least one hotel", ISBLANK ( _type ), "Select one code type", _n = 1, "Apply to 1 hotel", "Apply to " & _n & " hotels" )

Please check Part 2, as the post is a little lengthy.