1. Custom & conditional columns
1.1. Custom column allows you to write your own PowerQuery M formulas
1.1.1. Click on the attached link to a reference for a bunch of built-in Power Query M functions you can use in a custom column expression
1.1.2. One real-world use I found for this was creating a column to hold a literal date value for all rows; the following expression was used to create a RowEndDate column as part of some data modeling work
1.1.2.1. #date(9999, 12, 31)
1.1.3. Here's a meaningless example but one that highlights how a custom column can be used to overcome the limitations of conditional columns, implementing more complex conditional logic that can involve multiple columns
1.1.3.1. Example Flag based on multi-column conditional logic
1.1.3.1.1. Result
1.2. We can add a conditional column using basic If-Else If-Else logic
1.2.1. Example: adding a custom index to enable bespoke sorting of currency codes in Power BI (i.e. allow us to break with the default alphabetic sort order for text fields)
1.2.1.1. Add Sort Order column using conditional column
1.2.1.1.1. Result
2. Creating and using parameters
2.1. Parameters allow us to filter data across our model so that the report is more focussed.
2.2. For example, we can filter the data model for the USD currency, which in this example means applying a parameterised filter to 3 tables that reference currency
2.2.1. In Query Editor Home, click Manage Parameters | New Parameter (in this example, we name the parameter Ccy and set it to USD from a list of available currencies)
2.2.1.1. Move the new parameter to the Parameter Queries group - the next step is to apply the parameter to the 3 highlighted tables in our data model
2.2.2. Applying the parameter to each appropriate table in the data model involves applying a regular filter on the currency column and then editing via Advanced Editor to replace the literal filter value with a reference to the parameter
2.2.2.1. Add a filter to Currency table (the choice of currency is arbitrary)
2.2.2.1.1. In Advanced Editor, go to the new filter transformation, select the literal filter value and replace it with a(n unquoted) reference to the parameter (Ccy)
2.2.3. When you click Close & Apply to exit Query Editor and return to the main Power BI Report Editor, the parameterised filtering applies to the data loaded into the report's data model
2.2.3.1. We can quickly knock up a couple of visualisations
2.2.3.1.1. Now click Transform data | Edit parameters
3. Custom functions
3.1. These are built on top of query parameters and the option to create one from an existing query is only enabled when that query has at least one parameter
3.1.1. Example: imagine our Sales table has millions of rows and our report just needs to be for USD sales for 2016 made via the Export channel
3.1.1.1. We start by adding parameters to our Sales query for Channels and Years (see parameters branch above - this already created a Ccy parameter on the Sales query)
3.1.1.2. We right click the Sales table and choose Create Function
3.1.1.2.1. Name the function and click OK
3.1.1.3. Rename the Invoked Function appropriately (e.g. USD Export Sales 2106)
3.1.1.3.1. Tidy up the Queries by moving the new (renamed) invoked function into the Data Model group, moving the Sales query to Staging Queries and disabling the load for that, and move all the parameters and new function out of its custom group into the Parameters Query group
4. Staging queries
4.1. These are source tables that you want to combine or merge with others to make a final table in the data model
4.1.1. First step is to group them under "Staging Queries" (select tables and choose New Group)
4.1.1.1. In this example, the 3 Sales tables are identical apart from the year they relate to, so we want to combine these into 1 table, and the Channel Details table is a very small table that we want to merge into another
4.1.2. Next step is to prevent the table from being loaded because we don't want them brought in to the model
4.1.2.1. Right-click and un-check the "Enable load" option
4.1.2.2. Note how the table name becomes italicised when the load is disabled
5. Appending queries
5.1. The idea here is to take two or more staging queries with the same columns and union them together in a single table for the data model
5.1.1. First step is to make one of the staging queries the reference and put that into the data model
5.1.1.1. This creates a table that we just need to rename (e.g. to Sales)
5.1.1.1.1. Then move into Data Model
5.1.2. Next step is to use Home | Append Queries with the new reference table selected
5.1.2.1. Repeat as appropriate for all staging queries and note the applied steps for your data model table
5.1.2.1.1. In Advanced Editor, we can see the logic clearly
6. Merging or joining queries
6.1. This means joining a staging query to a data model table in order to bring lookup columns into that table
6.1.1. First step is to use Home | Merge Queries with the data model table selected
6.1.1.1. Select the natural key in the data model (e.g. Channel)
6.1.1.2. Select the staging query to merge (e.g. Channel Details)
6.1.1.3. Select the natural key in the staging query (values should match those in data model natural key)
6.1.1.4. Select the join kind (e.g. Left Outer)
6.1.1.5. Note how many matches are made before clicking OK
6.1.2. Next step is to expand the new table column added to the data model table by the merge
6.1.2.1. Click on the expand button in top right of the added table column
6.1.2.1.1. Select columns to add and click OK
6.1.3. Final step is to rename the expanded columns from the merge, as appropriate
7. Unpivoting queries
7.1. Whenever your table has two or more columns that essential hold the same type of data values, this is a good candidate for unpivoting
7.1.1. An example would be a table of exchange rates with a date column followed by multiple columns holding rates associated with different currency pairs
7.1.1.1. We can use this table to create an exchange rate fact table and the basis for a (related) currency dimension table, all based on unpivoting
7.1.1.1.1. Let's start by making the table a staging query (see details above) and referencing an Exchange Rates fact table and a Currency dimension table from that in our data model
8. Columns from examples
8.1. The idea of this is to quickly create columns in a more intuitive and efficient way
8.1.1. Example 1: add OrderDateKey column
8.1.1.1. Note that we have an OrderDate column, which is typed as date and we want to quickly create an OrderDateKey column that is an integer
8.1.1.1.1. Under Add Column, click Columns From Examples
8.1.2. Example 2: create composite key
8.1.2.1. We want to combine OrderNumber and OrderDate to make a key value - e.g. SO000101-20140601, SO000109-20140602, etc.
8.1.2.1.1. Under Add Column, click Columns From Examples