Leveraging PowerQuery to create columns with dates in headers and calculations throughout table

[JoeOrt]

I have to repeatedly build tables in Excel, write a bunch of COUNTIFS and then load that data into PowerQuery and unpivot it.

Does anybody have a way to do this quickly/automatically and in a repeatable manner? I've attached an example here where I have Start/End dates and I want to know how many lines were active in each month.



Bonus points if we can do it without M, but understand if that's the only way (I'm not an M coder).

 

[Chihiro]

Can you upload what end result should look like? I suspect you'd need M.

 

[JoeOrt]

I uploaded the intermediary step so let me go step by step in hopes that clarifies. For our example, let's say these people subscribe to a service for the dates included.

1) I start with a list of subscribers start and end dates:


Required outcome: I need to find out how many people were active on the first of the month from March 2015 to April 2016 from that list.

What I've been doing:
I add a bunch of columns with each month's start date:

Then I write COUNTIFS to compare the Start/End Dates with the header's date:
=COUNTIFS([@[Start Date]],"<="&Table1[[#Headers],[3/1/2015]],[@[End Date]],">"&Table1[[#Headers],[3/1/2015]])

I repeat that for each column I created.

I then take that table and load it into PowerQuery/PowerBI and unpivot the date columns:

Output:


Now I have the data in a way I can analyze:


If there's an easier approach to doing this, I'll all for it!

 

[Chihiro]

While it is possible to do what you are looking for in PowerQuery. It's far more efficient to do so in regular excel table and then upload resulting table to PowerBI for reporting etc.

See attached.

Edit: If you are getting original table from database and have write access to it (or if you have another database where you can replicate table/query i.e. Reporting database)... you can create Month start date table and use native query language to transform and join 2 tables (skipping Excel) and load directly to PowerBI

 

[JoeOrt]

Thanks, @Chihiro - the time periods always change and there are different tables where I have to repeatedly do it and there's always new data being added so I was hoping to automate it!

 

[Chihiro]

You can still automate it (to a point).

Step 1: Set up data import via PowerQuery to pull initial Start & End
Step 2: Create First of the Month table with calculation, allowing for future months (about a year)
Step 3: Set to refresh data on workbook open.
Step 4: Create batch file to open workbook and close after set time.
Step 5: Use Windows Task Scheduler to run the batch file
Step 6: Set up data connection in PowerBI to calculated table

Specifics will change depending on your set up and source table. You may need additional steps.

 

[Chihiro]

Here's way to do majority of steps in PowerQuery with minimal use of M.

However, you will still need to create Lookup table for Start of Month. There's no way of avoiding this (I'd suggest creating this table in database, but if that's not feasible Excel table will work just fine).

1. In Excel or Database create Start of Month table with index (Table2)
2. Load Table1 in PowerQuery as Connection only (Query2)
3. Load Start/End Date table (Table1) in PowerQuery as Connection only (Query1)
4. Edit Query2 and hide index and add Custom Column with M "=Table1" then expand that column. This creates Cartesian Product of the two tables.
5. Change column types of each column to "Date"
6. Add Custom column with M below. Note that it is case sensitive.
​

if [Custom.Start Date]>[Date] then "FALSE" else if [Custom.End Date]<=[Date] then "FALSE" else "TRUE"

7. Filter Custom Column for "TRUE"
8. Group by Start of Month column and Count of Rows​

Combined M for reference:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Date", type datetime}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"ID"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each Table1),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Start Date", "End Date"}, {"Custom.Start Date", "Custom.End Date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom.Start Date", type date}, {"Custom.End Date", type date}, {"Date", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [Custom.Start Date]>[Date] then "FALSE" else if [Custom.End Date]<=[Date] then "FALSE" else "TRUE"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = "TRUE")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Date"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"