Find unmatched records within 1 table with different months

[dadelta]

Hi,

I am working with 1 table in Power Query/Power BI.  This table contains 4 columns, 2 of which are a date column (last day of each month) and a unique ID column.  There are between 125,000 and 150,000 rows per month.  This table is going to be used to summarize the current months data and I need to get a count/list of items that appeared in the prior month but do not appear in the current month.  I'm able to do this in Power Query in Excel but I have 1 file for each month (essentially 2 tables).  Importing separate tables for each month is not really feasable.

How can I use the 1 table to find items where there is no match in the current month compared to the prior month?

Always appreciative of any assistance.

Thank you

[v-jayw-msft · Accepted solution]

Hi @dadelta ,

This table needs to contain data for this month and last month.

For example:

Then you could create a new table like below:

Table 2 = 
var this_month = CALCULATETABLE(VALUES('Table'[id]),FILTER('Table',MONTH('Table'[month end]) = MONTH(MAX('Table'[month end]))))
var last_month = CALCULATETABLE(VALUES('Table'[id]),FILTER('Table',MONTH('Table'[month end]) = MONTH(MAX('Table'[month end]))-1))
return
EXCEPT(last_month,this_month)

Best Regards,

Jay

[Ashish_Mathur]

Hi,

In the Query Editor, you will first have to append both tables into a single table.  Once that is done, we can write DAX formulas to get your desired result.  Share the link from where i can download your PBI file with both/all tables appended into a single table.