It’s about solving business problems & meeting people… Allen White interview

I’ve been remiss about blogging for a little while and have some great stuff to share, starting with this interview with Allen White at the PASS Summit conference in November. Allen is a 15-year PASS veteran and one of the most consistent SQL Server experts in the industry. He’s a powerhouse of knowledge, experience and wisdom. He’s a long-time SQL Server and Microsoft Data Platform MVP and owner of DataPerf Professionals Consulting, mentor, coach, trainer, co-founder and leader of the Ohio North SQL Server user group. He’s served on the PASS Board of Directors as the Program Director.

Allen shared some pearls of wisdom in this sit-down interview during PASS Summit 2018 in Seattle. He knows a lot about SQL Server tools and his first bit of advice for industry newcomers… “it’s not about tools.”

P: Allen, as an experienced leader in this industry what advice would you give to someone breaking in the industry or really thinking about planning their career around the Microsoft data platform?

A: People just starting out in the industry tend to do is focus on the tools in the technologies that’s the exciting thing. The problem is where the focus really needs to be is on the business problems that need to be solved. The tools are there to solve business problems; not in and of themselves. So, focus on what it is about the business that makes the tools useful to solve those business problems.

P: OK, and… so for a student or someone who has not immersed in facing business users and business leaders yet in their career; where does a person go to get that experience? What do they look for?

A: It’s a function of studying what it is about a particular line of business that…proposes the challenge. So, a transportation industry example… You’ve got trucks and they’ve got to get from point A to point B and you’ve got to measure that.. What tools out there into measures that? Do they have built-in GPS? Are they able to send those GPS signals to some centralized device that can even report on and track the progress of those vehicles etc.? …those kinds of things. Think about the business and the information needs that will help that business succeed.

P: Okay, good. What are you looking forward to most about PASS Summit this week?

A: Meeting the people. People make all the difference. My first Summit was 15 years ago and I’ve been to every one since and it’s always about the people.

Learn more about Allen and the PASS organization here.

Interviews from PASS Summit 2018: Matthew Roche, Matt Masson, Adam Saxton & Patrick LeBlanc

I had a chance to catch up with two of the three Matts from the Power BI product team at PASS Summit 2018.  Speaking with Matthew Roche and Matt Masson about the new Power BI Dataflows announcement, they told me that Power Query is becoming a truly enterprise-class data transformation technology. Matt Roche explained that not only is Dataflows a cloud-based implementation of Power Query with a browser-based query designer but it allows you to enforce business and data quality rules using strongly-typed entities in the Common Data Service.

Power BI Dataflows & Self-service Data Warehousing

Matt Masson told me that the expansion of Power Query into multiple products and services demonstrates how truly powerful the Power Query platform has become. He said that behind the simple-to-use user interface is an extremely flexible data transformation language, and underneath the M language is a great, flexible dataflow engine.  He’s currently working on the Intellisense features of the Power Query M language advanced editor. Matt Masson also said that he is expecting more third-party vendors to create custom data connectors for Dataflows and the Common Data Service.

I also caught up with Adam Saxton and Patrick LeBlanc from the Power BI Customer Advisory Team (CAT).  You may recognize Adam and Patrick from the “Guy In A Cube” video series.  Patrick described Power BI Dataflows as “self-service data preparation for the masses”. Adam added that that Dataflows takes Power Query to the next level by writing data to tables in Azure Data Lake Storage rather than directly to a VertiPaq data model.  This capability enables the Power BI platform to create a truly enterprise-scale data warehouse and Business Intelligence solution.

I asked Adam what skills are most important to someone growing a career in IT-based Business Intelligence.  He said that the most important thing is to understanding proper data modelling in data warehouse design.  Power BI – by itself is a great platform downstream from data sources or an existing data warehouse.  Dataflows and the CDS fills an important gap in self-service BI reporting & data analysis.  These technologies provide a common platform for data preparation, data warehouse design and standardized data entity management for self-service and BI reporting.  Dataflows shows a lot of promise for serious, next generation data warehouse solutions.  Stay tuned for more on this as I explore and prepare demos with Dataflows, Azure Data Lake Gen2 Storage and the Common Data Service. 

Data Model Options for Power BI Solutions

At the heart of every a Business Intelligence reporting  solution is a data model to optimize queries and enable ad hoc report interactions.  Data modelling technology has evolved quite a lot over the past 20 years or so.  You have several modelling technology choices with options depending on the simplicity or formality of your project and factors like data size and security.  In the past, choices were simpler.  Power BI was the choice for smallish, “good enough” projects; and when data quality, high volume and exactness were the priority, Analysis Services (SSAS) was the better choice.  Now, using Power BI for modelling larger data sets is even advantageous compared – with new features like hybrid models, aggregations and incremental data refresh.  Just in the past year or so enhancements have been added – or are in the process of being added – to the Power BI platform, that the list of options and considerations continue to grow.  Here’s a quick (and I mean quick) summarized list of the modelling options for Power BI solutions:

  • Power BI Desktop model published to the Power BI service – limited to 1 GB model size, no partitioning.  Pay per user with Power BI Pro licenses.
  • Power BI Desktop model published to a Premium Capacity Power BI Service – 12 GB size limit, dataflows & numerous “grown-up” features.  Unlimited read-only domain users.
  • Power BI Desktop model published to on-prem Power BI Report Server – limited to 2 GB model size, no partitioning.  Prior version & feature restrictions.
  • SSAS on-prem or AAS using report published to PBIRS – no model size limit.  Prior version & feature restrictions.
  • SSAS tabular or multidimensional on prem connected to Power BI report in service via the data gateway.  No size limits.  License server cores for SSAS.  License Power BI using Pro or Premium.
  • Azure Analysis Services = SSAS tabular.  No size limit, pay as you go and scale as needed.
  • Power BI reports in the service or PBIRS using DirectQuery to connect to data source with no cached model.  Report interaction queries may be slow, with some calculation limits.  *Aggregations & hybrid model design can optimize & reduce query latency, with moderately complex model development overhead (Premium features).

There are numerous advantages and disadvantages with each option so the best choice always clear but we can certainly compare the pros and cons.  I’ve been on a quest to find the best medium to break these choices down into simple terms but it truly is a journey and not a destination.  Along this journey, earlier this year, I presented a session called “The Nine Realms of Power BI” where I enumerated different categories of solution architectures for Power BI solutions; and they are numerous.

One important consideration is Microsoft’s commitment to support a product with new features in the future.  They have made it very clear that the Power BI platform is their primary focus and that they will continue to invest in enterprise-scale capabilities in the cloud service.  Never plan on a crucial feature being added later to a product but give serious consideration to  where a product is headed.

Making a side-by-side comparison of features between products and options is a little like comparing oranges, apples, grapes and bananas.  As a best effort, I started with the feature lists for SQL Server Analysis Services and added the Power BI variations.  Admittedly, this type of comparison doesn’t fit perfectly into this type of pivot format but I think it serves the purpose.  This post will likely evolve a bit with time.  Your feedback and input are welcome.

Some additional resources from trusted colleagues: Matthew Roche from Microsoft recently posted this article.  He refers back to this blog post and mentions an excellent session at the recent PASS Summit from Gabi Münster and Oliver Engels.  On Matt’s post, he links to the session slide deck that Gabi and Oliver shared.  An official whitepaper written by Chris Webb and Melissa Coates covers Power BI enterprise deployment options in 105 pages.  Keep in mind that many new features have been added since this paper was written but the whitepaper will likely be updated in the future.  Matt also references another recent on this topic by James Fancke where he contrasts features and costs.  This is, no doubt, a timely and important topic.

Guy-In-A-Cube’s Adam Saxton and Patrick LeBlanc have posted numerous short videos highlighting specific features of Power BI with Analysis Services and here are a few to get you started.  

Side-by-Side Feature Comparison

In the following table I compare capabilities, starting with SSAS Tabular and SSAS Multidimensional, with Power BI Pro and Premium capacity.  My blog theme doesn’t current support wide tables like this (apologizes for the overlapping text) so you can Open or download the Excel file here.  I hope this is helpful.  I’ll work on updates to optimize this comparison chart as features are added and time allows.

Feature
Enterprise/Developer
StandardAzure Analysis ServicesPower BI StandardPBI Report ServerPower BI PremiumComments
Max model size (compressed)No limit*16 GB No limit*1 GB2 GB10-12 GB**Premium supports 10 GB upload & 12 GB refresh.
Hybrid models
(DirectQuery & cached mode)
NoNoNoNoYesYes 
M/Power Query sources & transformationsYesYesYesYesYesYes**Query extensions in the service using dataflows
Integrated application lifecycle management (ALM) toolsYesYesYesNoNoNo 
Integrated version control toolsYesYesYesNoNoNo 
        
Tabular Models       
Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL, TMSL)YesYesYes*Yes*Yes*Yes*Third-party tool support, new XMLA endpoint for Power BI
HierarchiesYesYesYes**Yes*Yes*Yes**Simple hierarchies,
**AS supports HideMemberIfBlank
KPIsYesYesYesNoNoNo 
PerspectivesYes YesNoNoNo 
TranslationsYesYesYesNoNoNo 
DAX calculations, DAX queries, MDX queriesYesYesYesYesYesYes 
Row-level securityYesYesYesYesYesYes 
Multiple partitionsYes YesNoNoYes**Incremental refresh builds partitions
In-memory storage modeYesYesYesYesYesYes 
DirectQuery storage modeYes YesYesYesYes 
        
Multidimensional Models       
Semi-additive measuresYesNo 1Yes*Yes*Yes*Yes**Using DAX code,
Effort: moderate
HierarchiesYesYesYes**Yes*Yes*Yes**Simple hierarchies,
**AS supports HideMemberIfBlank
KPIsYesYesYesNoNoNo 
PerspectivesYes YesNoNoNo 
ActionsYesYes*Using 3rd party tool*Possible, limited*Possible, limited*Possible, limited 
Account intelligenceYesYesYes*Yes*Yes*Yes**Using DAX code,
Effort: high
Time intelligenceYesYesYesYesYesYes 
Custom rollupsYesYesYes*Yes*Yes*Yes**Using DAX code,
Effort: moderate
Writeback cubeYesYesNoNoNoNo 
Writeback dimensionsYes NoNoNoNo 
Writeback cellsYesYesNoNoNoNo 
DrillthroughYesYesYes*Yes*Yes*Yes**Multiple techniques
Advanced hierarchy types (parent-child and ragged hierarchies)YesYes*YesNoNoNo*Supports HideMemberIfBlank
Advanced dimensions (reference dimensions, many-to-many dimensions)YesYesYesYesYesYes 
Linked measures and dimensionsYesYes 2No*No*No*No**Equivelent functionality
TranslationsYesYesNoNoNoNo 
AggregationsYesYesYesYesYesYes 
Multiple partitionsYesYes, up to 3YesNoNoYes**Incremental refresh builds partitions
Proactive cachingYes *****In-memory model is always cached
Custom assemblies (stored procedures)YesYesNoNoNoNo 
MDX queries and scriptsYesYes*****Supports MDX queries & DAX scripts
DAX queriesYesYesYesYesYesYes 
Role-based security modelYesYesYesYesYesYes 
Dimension and cell-level securityYesYesNo*No*No*No**Equivelent functionality for measures
Scalable string storageYesYesYes*Yes*Yes*Yes**Equivelent functionality
MOLAP, ROLAP, and HOLAP storage modelsYesYesYes*Yes**cached or SSAS directYes**DirectQuery & hybrid models are equivelent or superior
Binary and compressed XML transportYesYesYes*Yes*Yes*Yes**VertiPaq in-memory compression on all data types
Push-mode processingYes YesYes*Yes*Yes**Multiple & different processing techniques supported
Direct writebackYes NoNoNoNo 
Measure expressionsYes YesYesYesYes 

Interviews with Microsoft Data Community Speakers and Leaders

What is the key to advancing your career in the Microsoft data platform?  Here is some advice from some of the most successful people in the industry…

Every year we have some big community events that bring together community leaders from all over.  These are international user group and community leaders who write books and speak at conferences. we had our local Oregon SQL Saturday “SQL Train” which is a chartered train – some coaches that one of our speaker’s chartered to bring all of our speakers and attendees up to the PASS summit after Oregon SQL Saturday, and then the big PASS summit (the big conference up in Seattle). I had a chance to sit down with a number of our speakers, community leaders and attendees and just ask questions about what brought them there, and advice that they would give people in the industry about how to get the most value out of that experience …and this is what they said:

Well-run monthly user group meetings and big annual events local events like SQL Saturday don’t just happen by themselves. It takes a lot of planning, a lot of volunteers and a lot of coordination to make these events successful. Part of that effort are the annual leadership planning meetings that we have during the week of PASS summit. Here are some short clips from those meetings where several hundred local leaders from all over the world got together to share ideas, to exchange notes and to coordinate to be able to make these events successful. Leaders cross-pollinate, exchange ideas and they work together to make this a great community. Why? …because somebody did that for us when we were getting started and we want to give back to the community. So, get involved; join the leadership committees at your local user groups, volunteer at SQL Saturday. Volunteer to do a short speaking engagement. Just get up and talk to some of your peers. Get started by volunteering in the community so that you can be part of the ongoing great community we have around the Microsoft data platform.

How to Configure the Power BI Gateway to use Dataset Connection Parameters

A service provider or vendor might want to publish multiple copies of a report that should connect to different database servers or databases.  In a true multitenant service solution, we would have a singe database with row-level user mapping tables that filter data by the logged in user.  True multitenant solutions require quite a lot of planning and development work to implement.  In smaller-scale or interim solutions, copies of a report can be deployed to different workspaces and then queries can be parameterized to use different database connections.

In this post, I’ll demonstrate deploying and configuring such a solution where the server name and database name have been parameterized and setup to use the on-premises gateway to connect and refresh data.  I’ll also setup scheduled refresh.  The full video walk-through is below but I’ll do a quick review to set the stage.

This is the Power Query Editor in Power BI Desktop.  I have two parameters that are used to specify the ServerName and DatabaseName for each SQL Server query:

Power BI Gateway with Parameters (Time 0_00_57;24)

Once deployed to a workspace in the service, the gateway must be configured with a data source for every possible server name and database combination.  In my example, I can connect to my local server using the NetBIOS name, IP address or LocalHost.  These all are acceptable methods but a data source must be added to the gateway configuration for each so the that the connection strings match exactly.  Remember that the connection is from the on-prem gateway to the database server so names like LocalHost or an internal IP address will work just fine.  In my example, I’m using the IP address of my local loopback adaptor on the local machine to connect to a local instance of SQL Server over the TCP connection.

Power BI Gateway with Parameters (Time 0_04_31;15)

In the workspace, the dataset is bound to the gateway.  Click the ellipsis and choose Settings.

Power BI Gateway with Parameters (Time 0_02_44;22)

To bind the gateway to the dataset, click to select the radio button next to the gateway.  This flips the switch titled “Use a data gateway”.  Apply the setting and then you can refresh the data or schedule refresh.

Power BI Gateway with Parameters (Time 0_05_47;00)Power BI Gateway with Parameters (Time 0_06_09;27)Finally, the parameters can be updated right here in the dataset settings.

Power BI Gateway with Parameters (Time 0_06_46;21)

 

Video Demonstration

 

Pareto, Burn-down & Accumulating Trend Charts in Power BI

I’m managing an Agile team project using Microsoft Teams – the new project management platform integrated with Office 365.  Teams is a simple and useful project management tool but it’s new and light on features.  Using Power BI, we want to show the hourly task burn-down for each two-week sprint.  In JIRA and some other more mature project management platforms, the burn-down chart is a standard feature in the tool that shows the number of hours or story points remaining, compared to the estimated number for the sprint.  Just as I began working on that, a client asked for some help creating a Pareto chart and it occurred to me that burn-down and Pareto charts are very similar variations of the same type of chart presentation.  These are not so much chart types as they are a set of calculations and techniques for displaying a desired result.

Project Hours Burn-Down Chart

Here’s the Burn-down chart showing days of the sprint on the X-axis and the hours for all resources as columns.  The burn-down line represents the number of estimated hours remaining for the duration of the sprint as a percentage of the total estimated hours for all resources.

imageHere’s another variation of the burn-down chart using stacked columns for each project resource, in my example; developers named Marta, Rob and Vivek.  Again, the burn-down line shows the daily percentage of estimated hours remaining compared to the total hours that have been “burned” for all days up to and including the current day.

image

The columns in the chart are simply the sum of hours reported.  I didn’t even create a measure for this value.  It’s just a summable column in the ‘Project Hours’ table named “Hours”.  Here’s a look at the data, including the measures used in the chart:

image

…and finally, here is the DAX measure code used for the line part of the column/line combination chart:

Project Hours Remaining % =
VAR DayAccumHours =
CALCULATE( SUM( ‘Project Hours'[Hours] ),
FILTER( ALLSELECTED( ‘Day’ ),
‘Day'[Day Number] <= MIN( ‘Day'[Day Number] )
)
)
VAR ProjectHoursRemaining = SUM( ‘Sprint Estimates'[Est Hours] ) – DayAccumHours

RETURN
DIVIDE( ProjectHoursRemaining, SUM( ‘Sprint Estimates'[Est Hours] ) )

Pareto Chart

This style of chart is very similar but the key differences are that the columns are ordered by the contribution value in descending order.   You can see that my sample dataset just uses numbers for the categories (e.g. “8”, “2”, “4”, etc.) but these could just as easily be names of resources, sales people, customers or products.  The columns are in descending order of aggregate value rather a time series of the axis field value.  To order by measure or value (my aggregate field is literally called “Value”), set the Type property for the X-axis to “Categorical”.

SNAGHTMLc49dd9d

Here’s the Pareto chart.  The contribution line shows the percentage of the total contribution that a category item and all of it’s predecessors in ranked descending order have made to that point.  For example, the first four top-ranking categories (8, 2, 4 and 5) account for about 50% of the total.

image

The DAX measure code for the accumulating percentage value used for the line, is as follows:

Accum Value % All =
VAR AccumValue =
CALCULATE( SUM(‘Pareto Series'[Value] ),
FILTER( ALLSELECTED( ‘Pareto Series’ ), ‘Pareto Series'[Value] >= MIN( ‘Pareto Series'[Value] ) )
)
VAR AllSeriesTotal = CALCULATE( SUM( ‘Pareto Series'[Value] ), ALLSELECTED( ‘Pareto Series’ ) )

RETURN
DIVIDE( AccumValue, AllSeriesTotal )

Accumulating Trend Series or Trend Chart

For good measure, I have one more example.  I actually started experimenting with calculation techniques using this chart.  It is essentially the burn-down chart example with the line presenting the opposite of the burn-down, so maybe this is a “burn-up chart” – I don’t know.  This may be useful when you want to visualize a time or other continuous series on the X-axis and the accumulating contribution as a line along with the actual contribution values as columns.

image

…and the measure DAX code used for the trend line:

Accum Series Value % All =
VAR AccumSeriesValue =
CALCULATE( SUM(‘Pareto Series'[Value] ),
FILTER( ALLSELECTED( ‘Pareto Series'[Period] ), ‘Pareto Series'[Period] <= MIN( ‘Pareto Series'[Period] ) )
)
VAR AllSeriesTotal = CALCULATE( SUM(‘Pareto Series'[Value] ), ALLSELECTED(‘Pareto Series’ ) )

RETURN
DIVIDE( AccumSeriesValue, AllSeriesTotal )

I’m hopeful that this will be useful and save you some time and energy.  As always, please leave comments and reach-out if you need some help.

You can download the example Power BI Desktop file here: Simple Pareto & Burn-down Chart Example.zip

SQL or M? – SSAS Partitions in Power Query/M

This is a continuation of this post

In the data platform industry, we have been working with SQL for decades.  It’s a powerful language and over many years, we’ve learned to work with it’s strengths and to understand and work around it’s idiosyncrasies.  M is a considerably more modern and flexible query language.  Some best practices have evolved but many are still learning the basic patterns of effective query design.  Reaching that stage with a technology often takes years of trial-and-error design and a community willing to share their learnings.  I will continue to share mine and appreciate so many in the community who share theirs.

Why Use M Instead of SQL?

For database professionals using SQL Server as the sole source of data for an SSAS or Power BI data model, there is a solid argument to be made in favor of encapsulating the query logic in database objects.  DBAs need to manage access to important databases.  A comment posted in an earlier post on this topic mentioned that SQL Server views can implement schema binding – which doesn’t allow a table or any other dependent object to be altered in such a way that it would break the view.  This is a good design pattern that should be followed if you are the database owner, have the necessary permission and flexibility to manage database objects as part of your BI solution design.  Ultimately, this is an organizational decision.  If the BI solution developer is not the DBA, you may have limited options.  If you don’t have control over the source database objects, if you are not using SQL Server or otherwise prefer to manage everything in the SSAS or Power BI project, Power Query is probably the right place to manage all the query logic.

In my earlier post, I used a table-valued user-defined function to manage the partition filtering logic in SQL Server.  Rather than using SQL and database objects, we’ll use Power Query alone.  The working M script is shown below.

For brevity, I’m starting by showing the solution but I will show you the steps we went through to get there a bit later.

Updating the Partition Definitions

The three partitions defined in the earlier example are replaced using the following M script, which returns exactly the same columns and rows as before.

image

Here is the M script for each partition:

“This week” partition:

let
Source = #”SQL/localhost;ContosoDW”,
SalesData = Source{[Schema=”dbo”,Item=”FactSalesCompleteDates”]}[Data],
#”Filtered Rows” =
Table.SelectRows( SalesData, each
[DateKey] >=
Date.StartOfWeek( DateTime.FixedLocalNow() )
)

in
#”Filtered Rows”

“This month before this week” partition:

let
Source = #”SQL/localhost;ContosoDW”,
SalesData = Source{[Schema=”dbo”,Item=”FactSalesCompleteDates”]}[Data],
#”Filtered Rows” =
Table.SelectRows( SalesData, each
[DateKey] >=
Date.StartOfMonth( DateTime.FixedLocalNow() )
and
[DateKey] <
Date.StartOfWeek( DateTime.FixedLocalNow() )
)

in
#”Filtered Rows”

“Before this month” partition:

let
Source = #”SQL/localhost;ContosoDW”,
SalesData = Source{[Schema=”dbo”,Item=”FactSalesCompleteDates”]}[Data],
#”Filtered Rows” =
Table.SelectRows( SalesData, each
[DateKey] <
Date.StartOfMonth( DateTime.FixedLocalNow() )
)

in
#”Filtered Rows”

The Power Query Litmus Test: Query Folding

When connected to an enterprise data source like SQL Server, Power Query should be able to pass an important test.  Use the Design… button to view the Power Query Editor.  Select the last query step and then right-click to show the menu.

image

If the View Native Query menu option is enabled, you are good.  This means the the query is being folded – and that’s a good thing.  Query folding converts the query steps into a native query for the database engine to execute.  This is the resulting T-SQL query script generated by Power Query:

select [_].[SalesKey],
[_].[DateKey],
[_].[channelKey],
[_].[StoreKey],
[_].[ProductKey],
[_].[PromotionKey],
[_].[CurrencyKey],
[_].[UnitCost],
[_].[UnitPrice],
[_].[SalesQuantity],
[_].[ReturnQuantity],
[_].[ReturnAmount],
[_].[DiscountQuantity],
[_].[DiscountAmount],
[_].[TotalCost],
[_].[SalesAmount],
[_].[ETLLoadID],
[_].[LoadDate],
[_].[UpdateDate]

from [dbo].[FactSalesCompleteDates] as [_]

where [_].[DateKey] >= convert(datetime2, ‘2018-07-01 00:00:00’) and [_].[DateKey] < convert(datetime2, ‘2018-07-15 00:00:00’)

You don’t need to do anything with this information.  It’s just good to know.  End of story.

And Now… The Rest of The Story

Power Query is an awesome tool that does some amazingly smart things with the simple data transformation steps you create in the designer.  However, it is important to make sure Power Query produces efficient queries.  During the development of this solution, I created an early prototype that didn’t produce a query that would fold into T-SQL.  Thanks to Brian Grant, who is an absolute genius with Power Query and M, for figuring this out (BTW, you can visit Brian’s YouTube tutorial collection here).

In my original design which I prototyped in Power BI Desktop, I thought it would make sense to create custom columns for each of the date parts needed to filter the partitions.  Here’s the prototype query for the query I originally named “This Month Thru Last Week”:

let
Source = FactSales,

#”Add DateTimeNow” = Table.AddColumn(Source, “DateTimeNow”, each DateTime.LocalNow()),
#”Change Type DateTime” = Table.TransformColumnTypes(#”Add DateTimeNow”,{{“DateTimeNow”, type datetime}}),
#”Add StartOfThisWeek” = Table.AddColumn(#”Change Type DateTime”, “StartOfThisWeek”, each Date.StartOfWeek([DateTimeNow]), type date),
#”Add StartOfThisMonth” = Table.AddColumn(#”Add StartOfThisWeek”, “StartOfThisMonth”, each Date.StartOfMonth([DateTimeNow]), type date),
#”Add StartOfPreviousMonth” = Table.AddColumn(#”Add StartOfThisMonth”, “StartOfPreviousMonth”, each Date.StartOfMonth(Date.AddMonths([DateTimeNow], -1)), type date),
#”Partition Filter” = Table.SelectRows(#”Add StartOfPreviousMonth”, each ([DateKey] >= [StartOfThisMonth] and [DateKey] < [StartOfThisWeek]) ),
#”Removed Columns” = Table.RemoveColumns(#”Partition Filter”,{“ETLLoadID”, “LoadDate”, “UpdateDate”, “DateTimeNow”, “StartOfThisWeek”, “StartOfThisMonth”, “StartOfPreviousMonth”})

in
#”Removed Columns”

As you can see, I created separate columns using Transform menu options based on the current date and time, stored in a custom column named “DateTimeNow”:

  • StartOfThisWeek
  • StartOfThisMonth
  • StartOfPreviousMonth

The rest was simple, I just added filters using these columns and then removed the custom columns from the query in the last step.  All good with one small exception… it didn’t work.  We learned that Power Query can’t use custom column values to build a foldable filter expression.  The filters just won’t translate into a T-SQL WHERE clause.

Checking the last query step with a right-click shows that the “View Native Query” menu option is grayed-out so No Folding For You!

image

Simple lesson: When query folding doesn’t work, do something else.  In this case, we just had to put the date comparison logic in the filter steps and not in custom columns.