Forum Discussion

zcaruso's avatar
zcaruso
New Member
5 years ago
Solved

No Record Group By

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:

 

YearMonthEmployeePoints
2020JanuaryJohn Smith1
2020AprilJohn Smith2
2020MayJohn Smith3

 

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:

 

YearMonthEmployeePoints
2020JanuaryJohn Smith1
2020FebruaryJohn Smith0
2020MarchJohn Smith0
2020AprilJohn Smith2
2020MayJohn Smith3

 

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

Need Help
  • artemus's avatar
    artemus
    5 years ago

    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"

     

     

2 Replies

  • artemus's avatar
    artemus
    Microsoft Employee
    5 years ago

    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"

     

     

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    5 years ago

    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.