Allow compound keys in relationships
Allow data modellers to define more complex relationships between tables. Currently, no compound key relationships are allowed, which hinders modelling and design, as now what should be a simple process becomes more complicated with users needing to manufacture unique non intelligent keys.
I support this - the lack of composite key support in the query editor infers that Microsoft demands a surrogate key, which is senseless. Modelling should be allowed to create the (minimum) required key to support the business intention, and the tool reading from the datastore needs to support this.
I've just hit this in an otherwise poorly modelled legacy store which I wanted to use PowerBI to give the business faster, more collaborative access to the data.
I will work around this gap by creating the join in a view. That is going to mean I need a deployment in order to create that view.
Greg Grater commented
The concatenation solution does not work in direct query mode. When there are large tables, it is not practical to use import mode. Please fix this.
I totally agree - this is a competitive disadvantage in the Microsoft BI stack today. Creating artificial keys could introduce unintended problems, not to mention it's non-value-added time and extra data to haul around.
Andrew Tobin commented
It'd be nice, but a simple alternative is to create another column on the dataset that concatenates fields to create a compound key.