Yesterday we discussed how to count the same value of different fields in one line (For example, how many “OK” in a line) in Business Central, I received a lot of feedback, which was much appreciated.
When building Power BI reports we often need to join two (or more) tables together, but what if the relationship is defined by two or more columns? Relationships in Power BI are limited to single columns, but whilst this seems like a major limitation there is actually a simple solution to create a relationship with multiple columns in Power BI.
To create a relationship with multiple columns in Power BI we simply need to create a new column by merging the required columns together. What’s more, if we use the same name in both queries Power BI will automatically create the relationship for us.
A few years ago I blogged about the Table.Profile M function and how you could use it to create a table of descriptive statistics for your data:
Since that post was written a new, optional second parameter has been added to the function called additionalAggregates which allows you to add your own custom columns containing aggregate values to the output of Table.Profile, so I thought I’d write a follow-up on how to use it.
The new Relationships view (or Modeling view – it seems to have two names) in Power BI Desktop that has been in preview since November 2018 not only makes it easier to work with complex models and set properties more easily, it also exposes a brand new property on a column: the “Is nullable” property. It’s visible at the bottom of the new Properties pane when you click on a column:
The order of the columns in a table in a Power BI dataset doesn’t matter all that much, especially because the Fields pane in Power BI Desktop ignores the original column order and lists the columns in a table in alphabetical order. However there are a few situations where it is important, for example when you are using the DAX Union() function in a calculated table: as the documentation states, when you use Union() “Columns are combined by position in their respective tables”. You might also find it irritating if the columns you see in the Data or Relationships panes in the main Power BI Desktop window make it hard to browse the data or create relationships.