Default Data Rows

In a perfect world, all of our data models within Power BI would be served from a properly curated Enterprise Data Warehouse with all rows having data populated for the appropriate "parent" data via (at least implied) referential integrity. This tends to take the form of the "child" column effectively being treated as NOT NULL and having a parent key pointing to a "Not Specified" record.

Of course we do not live in this data Valhalla, and as such we often have to grab crappy datasets from wherever we can get them, and need to plug in some of these dummy values into both the reference table, and then replace the null values in the referencing table with the appropriate ID.

Having done this once too often by hand (and continuing my "Constructive Laziness" mantra - i'll talk about this some other time), I decided that I'd do it better..

The upshot was a function which you can call as part of getting the reference table data which INSERTs the extra data row into that dataset

As this stands at the minute, one of the downsides is that it turns all of the column datatypes to "Any" which is a bit of pain.

Once we've got the extra row into the parent table, we need to update any potential Null rows in the child table so that it'll point to the parent

Now that this is done, Power BI (and your users) can be much happier with relationships created between the tables without any worrying about optional FK lookups