Excel Power Query - checking date in a list of dates using M

Question

I have to compare if a date in table A is present in all of the dates column in table B, return 1 if yes and no otherwise. I am using Excel Power Query and my formula is this:

Table.AddColumn(#"Renamed Columns", "BL_Count", each if List.Contains(Dates[Dates_WeekStart],[BL_WeekStart]) then 1 else 0)

I get all as 0s though. What am I doing wrong?

Dates table has all weekStart dates from 2015 onwards. There is no other column in that table. BL_WeekStart has various WeekStart values, I need to compare if that date is present in Dates and return a 1.

You can merge both tables. Expand and them check the result in a conditional column — Ricardo Diaz, Nov 23 '19 at 01:38

score 0 · Answer 1 · answered Nov 23 '19 at 22:09

0

Your code should work. Check types of Dates_WeekStart and BL_WeekStart columns - they should be the same. If the column has type date and another one - datetime then you always will get zeros.

answered Nov 23 '19 at 22:09

Aleksei Zhigulin

1,594
1
8
11

Excel Power Query - checking date in a list of dates using M

1 Answers1