Add a dynamic "Days of Last Month" column in Query Editor

[Anonymous]

I'm trying to add a custom column to my table that would dynamically list the days of last month.

Example: 

In October the table would have rows:

• 1/9/18
• 2/9/18
• ...
• 29/9/18
• 30/9/18

In November:

• 1/10/18
• 2/10/18
• ...
• 30/10/18
• 31/10/18

I've been drilling into the Date functions (documented here) but can't see one that would cater to this requirement.

Is this function possible?

[ChrisMendoza]

@Anonymous,

What is the correct answer for when the previous month exceeds the current month, e.g. Nov has 30 days where Oct has 31? Also notice rows 30 & 31 where Sept 30 is repeated.

Did you try Date.AddMonths ( )?

Of course I am presuming you have a Date column with a contiguous date range.

Custom Column = Date.AddMonths ( [Date], -1 )

[Anonymous]

I don't currently have a column of the current month. I'm happy to add whatever else I need to make this work though.

Edit: I should state that I don't know of a function that just lists all of the days of the current month. I anticipate the same issue you raised for months when there are 30 vs 31 days and using a function that is (date) - 1.

The issue I'm trying to solve is that I have 2 sets of data, with no unique fields to create a relationship. So I was trying to create some sort of "joiner" table of data that could hold unique dates and relate those to the duplicate dates in each other table.

I suppose another thing I could investigate is copying a column from another table and just removing duplicates. Bit hacky but it might serve..

[Anonymous · Accepted solution]

I ended up merging both queries based on the date column. After expanding the merge, filtering some blank/"null" rows, removing duplicates and filtering appropriately I was able to cobble together my solution.