No Record Group By

[zcaruso]

Hello,

I'm grouping monthly attendance data and i ran into a problem when i had to upload it to another program. This is what i'm currently getting for my group by:

Year	Month	Employee	Points
2020	January	John Smith	1
2020	April	John Smith	2
2020	May	John Smith	3

There were only entries in January, April, and May for John Smith so the months where there were no entries were left out. I need the output to look like this:

Year	Month	Employee	Points
2020	January	John Smith	1
2020	February	John Smith	0
2020	March	John Smith	0
2020	April	John Smith	2
2020	May	John Smith	3

Null is fine too, i just need a value associated for each employee for each month. I would like to be able to do this in power query editor only if possible. Can't share the data for security reasons.

Thanks,

Zach

[v-alq-msft]

Hi, @zcaruso 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

After 'Grouped':

You may paste the following m codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0lHyys/IUwjOzSzJAHIMlWJ1opVMcMga4JU1Asua4jXZVN8Il95YAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Employee = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Employee", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Month", each Date.MonthName([Date])),
    Custom1 = Table.AddColumn(#"Added Custom1","MonthNo",each Date.Month([Date])),
    #"Grouped Rows" = Table.Group(Custom1, {"Year", "MonthNo", "Employee"}, {{"Points", each List.Sum([Value]), type nullable number}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "New", each let 
c = [MonthNo],
next = 
List.Min(
   Table.SelectRows(#"Grouped Rows",each [MonthNo]>c)[MonthNo]),
l = List.Generate(
   ()=>c,
   each _<next,
   each _+1
),
m = List.Max(#"Grouped Rows"[MonthNo])
in 
if [MonthNo]=m then {m}
else l),
    #"Expanded New" = Table.ExpandListColumn(#"Added Custom2", "New"),
    Custom3 = Table.AddColumn(#"Expanded New","NewPoints",each if [MonthNo]=[New] then [Points] else 0),
    Custom2 = Table.TransformColumns(Custom3,

 {
   "New",each
   if _=1 then "January" 
   else if _=2 then "February"
   else if _=3 then "March"
   else if _=4 then "April"
   else if _=5 then "May"
   else if _=6 then "June"
   else if _=7 then "July"
   else if _=8 then "August"
   else if _=9 then "September"
   else if _=10 then "October"
   else if _=11 then "November"
   else if _=12 then "December"
   else null 
 }
 

),
    #"Removed Columns" = Table.RemoveColumns(Custom2,{"MonthNo", "Points"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"NewPoints", "Points"}})
in
    #"Renamed Columns"

Result:

Best Regards

Allan

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

[artemus · Accepted solution]

Here you go, just replace MyTable with your table:

let
    Source = List.Generate(() => #date(2020, 1, 1), each _ < #date(2022, 1, 1), each Date.AddMonths(_, 1)),
    Custom1 = #table(type table [Date = date], List.Zip({Source})),
    #"Inserted Year" = Table.AddColumn(Custom1, "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month Name",{"Date"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Employee", each List.Distinct(MyTable[Employee]), type {text}),
    #"Expanded Employee" = Table.ExpandListColumn(#"Added Custom", "Employee"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Employee", {"Year", "Month Name", "Employee"}, MyTable, {"Year", "Month", "Employee"}, "MyTable", JoinKind.LeftOuter),
    #"Expanded MyTable" = Table.ExpandTableColumn(#"Merged Queries", "MyTable", {"Points"}, {"Points"})
in
    #"Expanded MyTable"