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.
Tag Archives: Column
Create a relationship with multiple columns in Power BI
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.
Adding Tooltips To Column Headers In The Power Query Editor In Power BI And Excel
Here’s something that will excite all you M nerds out there! Did you know you can make tooltips appear when you hover over column headers in a table in the Power Query Editor in either Power BI Desktop or Excel?
Adding More Aggregate Columns To The Output Of Table.Profile
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:
https://blog.crossjoin.co.uk/2016/01/12/descriptive-statistics-in-power-bim-with-table-profile/
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.
Query by Example to Extract YYYY-MM from a Date Column
I was answering a question for a student in one of my online training courses this week. The question was, “Do you know of a way in power query to efficiently extract YYYY-MM from a Date column?” This can be done ‘manually’ with multiple steps including:
The Is Nullable Column Property In Power BI
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:
Power BI And Column Order In M Queries And Dataset Tables
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.