Power Query show Missing Payments split by Month and Year (MM YYYY)

Question

I am trying to do the same as someone has already asked on a Microsoft forum

I am trying to show Payment info in Power BI but mainly what months/Years Payments have been missed. I have 3 Tables, Date Table, Payment Table and Client Table. The Payment Table has the fields,[Client ID],[Payment Id],[Payment Date], Client Table has fields [Client Id] and [Client Name] etc, Date Table has is generated using the min and max [Payment Date] from the Payment Table.

Is this possible in Power Query or using DAX in Power Bi and if so how?

Thanks

Answer 1

You can try something like this

code for new table, missing

let Source = Date,
#"Added Custom9" = Table.AddColumn(Source, "Month", each Date.Month([Date])),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Year", each Date.Year([Date])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom10",{"Date"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"Month", "Year"}),
#"Added Custom" = Table.AddColumn(  #"Removed Duplicates", "Client ID", each Clients[Client ID]),
NewDates = Table.ExpandListColumn(#"Added Custom", "Client ID"),

#"Added Custom12" = Table.AddColumn(Payments, "Month", each Date.Month([Payment Date])),
NewPayments = Table.AddColumn(#"Added Custom12", "Year", each Date.Year([Payment Date])),

 #"Merged Queries" = Table.NestedJoin(NewDates, {"Month","Year","Client ID"}, NewPayments, {"Month","Year","Client ID"}, "Payments", JoinKind.LeftOuter),
 #"Added Custom1" = Table.AddColumn(#"Merged Queries", "Paid?", each Table.RowCount([Payments])),
 #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([#"Paid?"] = 0)),
 #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Payments", "Paid?"}),
 #"Merged Queries1" = Table.NestedJoin(#"Removed Columns", {"Client ID"}, Clients, {"Client ID"}, "Clients", JoinKind.LeftOuter),
 #"Expanded Clients" = Table.ExpandTableColumn(#"Merged Queries1", "Clients", {"Client Name"}, {"Client Name"}),
 #"Grouped Rows" = Table.Group(#"Expanded Clients", {"Month","Year"}, {
    {"Missing Payments From", each Text.Combine(List.Transform([Client ID], Text.From), ","), type text},
    {"Missing Payments From2", each Text.Combine(List.Transform([Client Name], Text.From), ","), type text}
})
 in #"Grouped Rows"

remove the group step if you'd rather see the output in table format

Version 2 generates this:

//version2
let Source = Date,
#"Added Custom9" = Table.AddColumn(Source, "Month", each Date.Month([Date])),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Year", each Date.Year([Date])),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom10",{"Date"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns1", {"Month", "Year"}),
#"Added Custom" = Table.AddColumn(  #"Removed Duplicates", "Client ID", each Clients[Client ID]),
NewDates = Table.ExpandListColumn(#"Added Custom", "Client ID"),

#"Added Custom12" = Table.AddColumn(Payments, "Month", each Date.Month([Payment Date])),
NewPayments = Table.AddColumn(#"Added Custom12", "Year", each Date.Year([Payment Date])),

#"Merged Queries" = Table.NestedJoin(NewDates, {"Month","Year","Client ID"}, NewPayments, {"Month","Year","Client ID"}, "Payments", JoinKind.LeftOuter),
#"Added Custom1" = Table.AddColumn(#"Merged Queries", "Paid?", each Table.RowCount([Payments])),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([#"Paid?"] = 0)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Payments", "Paid?"}),
#"Merged Queries1" = Table.NestedJoin(#"Removed Columns", {"Client ID"}, Clients, {"Client ID"}, "Clients", JoinKind.LeftOuter),
#"Expanded Clients" = Table.ExpandTableColumn(#"Merged Queries1", "Clients", {"Client Name"}, {"Client Name"}),
#"Added Custom2" = Table.AddColumn(#"Expanded Clients", "MonthYear", each Text.From([Month])&"/"&Text.From([Year])),
#"Grouped Rows" = Table.Group(#"Added Custom2", {"Client ID","Client Name"}, {
    {"Missing Payments", each Text.Combine(List.Transform([MonthYear], Text.From), ","), type text}
        })
in #"Grouped Rows"