Power Query to Find Project Names which do not have update in current month

[dbollini]

I need to find Project names from Project master table by checking the monthly updates table if the particular project had update for that particular month and if it does not have any updates in monthly updates table then those project names with status should populate based on filter  we select which is  Month start date from monthly updates table

Below is the sql query used to get the data for projects not listed for the month and need to convert it in DAX or Power query

select * from projectmaster where projectstatus in ('On-Track', 'Delayed', 'At-Risk') and ID not in (select projectid from monthlyupdates where monthlyupdates.monthstartdate = '2024-09-01 00:00:00.000')

[v-mdharahman]

Hi @dbollini,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @dbollini,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @dbollini,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you want to retrieve project names from the project master table, but only for those projects that did not have an entry in the monthly uodates table for a selected month start date. 

Here is the code example in DAX and PowerQuery.

* DAX approach : 

ProjectsWithoutUpdate =
VAR SelectedMonth = SELECTEDVALUE(MonthlyUpdates[monthstartdate]) -- Get the selected month

RETURN
FILTER(
ProjectMaster,
ProjectMaster[ProjectStatus] IN {"On-Track", "Delayed", "At-Risk"} &&
NOT ProjectMaster[ID] IN
FILTER(
MonthlyUpdates,
MonthlyUpdates[monthstartdate] = SelectedMonth
)
)

* Power Query (M) Approach : 

let
ProjectMaster = Source{[Name="ProjectMaster"]}[Content],
MonthlyUpdates = Source{[Name="MonthlyUpdates"]}[Content],

// Filter Monthly Updates for selected month
SelectedMonth = Date.From(#"Your Filter Date Here"),
FilteredMonthlyUpdates = Table.SelectRows(MonthlyUpdates, each [monthstartdate] = SelectedMonth),

// Remove projects that exist in MonthlyUpdates
MergedTables = Table.NestedJoin(ProjectMaster, "ID", FilteredMonthlyUpdates, "projectid", "Updates", JoinKind.LeftAnti),

// Keep only relevant project statuses
FilteredProjects = Table.SelectRows(MergedTables, each List.Contains({"On-Track", "Delayed", "At-Risk"}, [ProjectStatus]))

in
FilteredProjects

I would also take a moment to thank @ArwaAldoud, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[dbollini]

For the Dax query i am getting this error: The number of arguments is invalid. Function CONTAINSROW must have a value for each column in the table expression.

[v-mdharahman · Accepted solution]

Hi @dbollini,

The error likely occurs because "CONTAINSROW" expects a single-column table or explicit values for each column in the comparison. To fix this, you need to correctly filter "MonthlyUpdates" and check if the "ProjectMaster[ID]" exists in it. 
You can also try use EXCEPT or LOOKUPVALUE in place of IN for better logic. 

ProjectsWithoutUpdate =
VAR SelectedMonth = SELECTEDVALUE(MonthlyUpdates[monthstartdate])

RETURN
FILTER(
ProjectMaster,
ProjectMaster[ProjectStatus] IN {"On-Track", "Delayed", "At-Risk"} &&
NOT ISBLANK(
LOOKUPVALUE(
MonthlyUpdates[projectid],
MonthlyUpdates[projectid], ProjectMaster[ID],
MonthlyUpdates[monthstartdate], SelectedMonth
)
)
)

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[ArwaAldoud]

You can use Power Query to find projects without updates by performing a Left Anti Join between the ProjectMaster table and MonthlyUpdates table.
1. Load both tables
2. Filter MonthlyUpdates for the selected month
3. Merge tables using a Left Anti Join to exclude projects with updates
4. Filter by status (On-Track, Delayed, At-Risk)
https://learn.microsoft.com/en-us/power-query/merge-queries-overview

Let me know if you need further details

If this response was helpful, please accept it as a solution and give kudos to support other community members