How can I find empty dates?

Hi, Anonymous 

Try this:
M query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdZbjoMwDAXQvfBdIdt5r6Xq/rdRBpPEJe70/hF0ZIVcx+L53Jglbo9NhIj4eOCdeBficjwH3ll08Xp8UjG0KqWgizsNK9WqK41Kg62afJqUZkurT7PSMijt1HxalFa7V/JpVdpMVWaftpP2jz5e1p3ORbtTJpzatJqNYKWC07BS/rIBm1azaa004fRKKw5KV8orLTitONW0+vZ0r8WlQqazetXmU8ap4DTgVNPSbv4/WEk4zTgtONW0+KNf/xbHgd9pm/16xtFbe6WBcMpzugxafSo4NWkpvdJyaJyDqFc9T86haQ6iXpX9wwoZpwWndQY79io+NZPwpH28HdfjRiPh1EzC856NYFcqK+UvVa+0ZFDS+eHQOKfLoNmnCacZp2XOrHECxacVp830a99Ac2kinDJOxTQh2yZcacCpmYQ/eiAlnGacFpyaSfiL2kko9sLInWa60XFhVso4FZwGnJq/jE65f9brDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, #"Invoice No" = _t, #"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Start Date", type date}, {"End Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Customer ID", Int64.Type}, {"Invoice No", Int64.Type}}),

    startDate=List.Buffer(#"Changed Type"[Start Date]),
    endDate=List.Buffer(#"Changed Type"[End Date]),
    
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Range", each List.Transform({Number.From([Start Date])..Number.From([End Date])}, each Date.From(_))),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1", {"Customer ID"}, #"Added Custom1", {"Customer ID"}, "MergeTable", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "AllRange", each List.Union([MergeTable][Range])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Calendar", each List.Transform({Number.From(List.Min(startDate))..Number.From(List.Max(endDate))}, each Date.From(_))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EmptyDate", each List.RemoveMatchingItems([Calendar],[AllRange])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom3",{"Range", "MergeTable", "AllRange", "Calendar"}),
    #"Added Custom4" = Table.AddColumn(#"Removed Columns", "Year-Month", each List.Distinct(List.Transform([EmptyDate], each Text.From(Date.Year(_))&"-"&Text.From(Date.Month(_))))),
    #"Added Custom6" = Table.AddColumn(#"Added Custom4", "Count_EmptyDateMonth", each List.Count([#"Year-Month"])),
    #"Added Custom5" = Table.AddColumn(#"Added Custom6", "yyyy-mm", each List.Accumulate([#"Year-Month"], "", (state, current) => state&", "&current)),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Custom5", {{"yyyy-mm", each Text.AfterDelimiter(_, " "), type text}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Extracted Text After Delimiter",{"EmptyDate", "Year-Month"})
in
    #"Removed Columns1"

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.