Read the identity map in Power BI

This page loads the identity map into Power BI, so a report can group records from different systems by person. The steps are for Power BI Desktop, and for the Power BI service when you publish. The last section covers Excel.

The identity map holds IDs and no personal details, so a report built on it doesn’t carry names or email addresses unless your own data does.

Note

Power BI changes its menus from time to time. The steps below follow Power BI Desktop as it stood in mid-2026, and the names of menus, buttons and dialogs may differ slightly in your version. The connector, the Basic credentials and the feed’s address are the same whatever the menus are called.

Before you start

You need a feed key: a user name that starts fk_ and a password. An admin issues one under Admin, then Sources, in the Feed keys card. Read the identity map has the steps. Make a key for Power BI alone, with a name such as Power BI finance.

To check the key before you open Power BI, put https://app.samesake.co.uk/odata/IdentityMap?$top=1 in a browser’s address bar. It asks for a user name and password: enter the feed key’s. You should see one row of JSON. If you see 401, the key is wrong or has been revoked.

Load the identity map

1. In Power BI Desktop, choose Get data, then OData feed. If it isn’t in the short list, choose More and search for OData.
2. For the URL, enter https://app.samesake.co.uk/odata, the feed URL that the Feed keys card shows. Choose OK.
3. Power BI asks how to sign in. Choose Basic, enter the feed key’s user name (fk_ and eight characters) and password, and choose Connect. Leave the level it applies the settings to at the default.
4. The Navigator lists IdentityMap and PersonRedirects. Tick both, and choose Transform Data.

To go straight to one table, use https://app.samesake.co.uk/odata/IdentityMap as the URL instead.

In the Power Query editor, check each column’s type. ModifiedAt and RetiredAt arrive as Date/Time/Timezone. The other columns are text. PersonId is empty for a record that matching hasn’t reached yet. Then choose Close & Apply.

Power BI reads the feed a page of 5,000 rows at a time and follows the feed’s next links by itself, so a large identity map loads without any setting. Each page is one request, and the feed allows 120 a minute for each feed key.

Join it to your own data

Each row of the identity map names a record by its source and its ID in that source: SourceSystemCode and SourceRecordId. Join your own tables on those two columns, and the PersonId tells you which records are the same person.

Power BI relationships use one column, so make one key from the two. In the Power Query editor, with IdentityMap selected, choose Add Column, then Custom Column, name it SourceKey and enter:

[SourceSystemCode] & "|" & [SourceRecordId]

Do the same in your own table, with the source’s code typed in, for example "CRM" & "|" & [ContactId], matching the ID that you send to Samesake. Then relate the two SourceKey columns in the model view, and group a report by PersonId. Take care over capital letters: the IDs are case-sensitive, so abc and ABC are different records.

Keep a stored person ID up to date

A person ID can retire when two people are merged. If your report or another system stores PersonId values, look them up in PersonRedirects: RetiredPersonId is the stored value, and CurrentPersonId is the ID to use now. A redirect always points straight to the current ID.

In the Power Query editor, merge your table with PersonRedirects: choose Home, Merge Queries, pick the stored ID column and RetiredPersonId, and use a Left Outer join. Expand CurrentPersonId, then add a custom column that takes it when there is one:

if [CurrentPersonId] <> null then [CurrentPersonId] else [StoredPersonId]

Replace StoredPersonId with the name of your own column. Refresh PersonRedirects in full each time, not incrementally: Read the identity map says why.

Refresh on a schedule

After you publish the report to the Power BI service, give the dataset the feed key again, because the service doesn’t use the one saved in Desktop.

1. In the Power BI service, open the dataset’s Settings, and under Data source credentials choose Edit credentials for the OData source.
2. Set the authentication method to Basic, enter the feed key’s user name and password, and choose a privacy level that fits your organisation’s policy.
3. Under Scheduled refresh, turn refresh on and pick the times.

The feed is on the public internet, so you don’t need an on-premises data gateway. If a refresh fails with an error about credentials, the key may have been revoked: check Admin, Sources, Feed keys.

What the feed shows follows matching, which runs in the background. A refresh that starts straight after your systems send a change may not have the new PersonId yet, so a daily refresh is usually enough.

Incremental refresh

By default each refresh reads the whole identity map. Start there: it’s simple, and it’s always correct. If a refresh gets too slow, use incremental refresh, which reads only the rows that have changed. It reads ModifiedAt, which moves when your system sends a change and when matching changes a PersonId.

Caution

Incremental refresh keeps rows by ModifiedAt, which is when a record last changed, not when it was created. A record that hasn’t changed for longer than the period you choose to store drops out of the model, and so does its person: the report no longer sees it. Because the identity map is meant to hold every record, either store a period far longer than any record goes unchanged, or use a full refresh. Read the policy before you turn it on.

Read only the period you need

Incremental refresh needs two things: parameters for the period to read, and a filter on ModifiedAt that uses them.

1. In the Power Query editor, choose Manage Parameters, then New Parameter. Make two, named exactly RangeStart and RangeEnd, both of type Date/Time. Power BI uses these names, with those capital letters. Give each a current value, such as the start and end of last month: Desktop uses them to preview the data.
2. With IdentityMap selected, choose Home, then Advanced Editor, and replace the query with the one below.

let
    Utc = (value as datetime) as text =>
        DateTimeZone.ToText(
            DateTimeZone.ToUtc(DateTimeZone.From(value)),
            [Format = "yyyy-MM-dd'T'HH:mm:ss'Z'", Culture = "en-US"]
        ),
    Source = OData.Feed(
        "https://app.samesake.co.uk/odata/IdentityMap",
        null,
        [
            Implementation = "2.0",
            Query = [
                #"$filter" =
                    "ModifiedAt ge " & Utc(RangeStart) & " and ModifiedAt lt " & Utc(RangeEnd)
            ]
        ]
    )
in
    Source

This puts the filter in the web address, as $filter=ModifiedAt ge 2026-09-01T00:00:00Z and ModifiedAt lt 2026-10-01T00:00:00Z, which the feed understands. The Utc step turns each value into UTC text with a fixed culture, so the colons and digits come out the same whatever your regional settings. We haven’t confirmed which time zone Power BI’s own RangeStart and RangeEnd are in. The query treats them as being in the time zone of the machine that runs it, and converts. That is safe for one reason: the same conversion is applied to both ends, so the periods still meet exactly, with no gap and no overlap. At worst the period shifts by an hour, which changes which period a row falls in, but no row is missed or read twice. Because the filter is in the query rather than a step Power BI works out for itself, Power BI may warn that it can’t tell the source folds the filter. The feed does apply it. Check that the rows you get fall inside the two dates.

Set the policy

1. Choose Close & Apply.
2. In the model, right-click IdentityMap and choose Incremental refresh.
3. Turn it on, and choose how much history to keep and how much to refresh each time. The period to store counts back from today by ModifiedAt. Make it longer than the oldest ModifiedAt in your identity map, and much longer than any record is likely to go without a change: a record whose ModifiedAt is older than the period isn’t loaded, and as time passes the period moves and the oldest periods are dropped. For the period to refresh, something like the last seven days is usual. A longer one costs more requests but catches more late changes.
4. Publish the report. The first refresh in the service reads all the stored history, a page at a time, and later ones read only the refresh period.

If you can’t choose a stored period that covers all the history for as long as you’ll use the report, use a full refresh instead.

Parallel refresh and the rate limit

Power BI divides the stored history into partitions, one for each month, quarter or year, and one for each day or so of the refresh period. It reads each partition with its own query, which is at least one request, and one for every 5,000 rows. It can run several at once. The feed allows 120 requests a minute for each feed key, so the first refresh in the service, which reads every partition, can reach the limit.

We don’t know whether Power BI waits for the Retry-After the feed sends with a 429, or fails the partition. If a refresh fails with a 429:

• Run fewer queries at once. In Power BI Desktop, look in the options for the current file, under data load, for the settings on loading in parallel. For a refresh started through Power BI’s enhanced refresh API, the request can set a maximum parallelism. Names and places vary between versions. Leave the OData connector’s Concurrent option off, which is its default, so that each query’s own pages are read in turn.
• Store a longer period in fewer, larger partitions: a coarser granularity means fewer queries.
• Don’t use the same feed key for another dataset while a refresh runs.
• Refresh again. Partitions that completed don’t need reading again.

Take the latest row for each record

Incremental refresh keeps rows in partitions by date, and a partition outside the refresh period is never read again. A record whose ModifiedAt moves, because a field changed or matching gave it a new PersonId, appears in the new period, but its old row stays where it was. After a while the table can hold several rows for one RecordId: an old one with the old PersonId, and the current one.

So keep only the latest row for each record. One way is a calculated column in the model:

IsLatest =
VAR LatestChange =
    CALCULATE (
        MAX ( IdentityMap[ModifiedAt] ),
        ALLEXCEPT ( IdentityMap, IdentityMap[RecordId] )
    )
RETURN
    IdentityMap[ModifiedAt] = LatestChange

ALLEXCEPT removes every filter on the table except RecordId, so the MAX is the latest ModifiedAt among the rows for that one record. The column is true for the current row, and false for the older ones.

Then use only the true rows, in one of these ways:

• A filter. Put IsLatest into the filters of a visual, a page or the whole report, and set it to true.
• A measure. Count records with CALCULATE ( COUNTROWS ( IdentityMap ), IdentityMap[IsLatest] = TRUE () ).
• A relationship. Duplicate rows make SourceKey appear more than once, which spoils a one-to-many relationship. Make a calculated table with IdentityMapLatest = FILTER ( IdentityMap, IdentityMap[IsLatest] ), relate your own data to it instead, and hide IdentityMap.

On a very large table the column takes a while to calculate, and that can cost more than a full refresh saves. If your identity map has fewer than a few million rows, a full refresh is usually the better choice.

A deleted record stays in the feed with Status set to Deleted, and the delete is its latest row.

Troubleshooting

What you see	What it means
Credentials are refused, or 401	The feed key is wrong, was revoked, or you used an API key. Use the feed key’s user name (it starts fk_) and its password, with Basic.
400 with “The query isn’t valid.”	Power BI sent an option the feed doesn’t accept, such as $apply (which it can produce for a Group By step) or $expand. Load the rows, and group them in the model instead.
429 “Too many requests”	The feed key has made more than 120 requests in a minute. Wait a minute and refresh again, and don’t share one key between many refreshes at once.
PersonId is empty for a record	Matching hasn’t reached it yet. Refresh again later.
Rows with the same RecordId more than once	Incremental refresh. See take the latest row.

Excel

Excel reads the same feed. On the Data tab, choose Get Data, then From Other Sources, then From OData Feed. Enter https://app.samesake.co.uk/odata, choose Basic and enter the feed key’s user name and password, then pick IdentityMap or PersonRedirects in the Navigator and load it. Refresh All reads it again. Excel’s menu names also vary between versions.