How to create a new column with the sum of values of another column based on criteria?

[Anonymous]

Hi, I am very new to this so I don't know better words to explain what I'm trying to do, but here it goes:

I have a table like this:

Employee	Type of Day Off	Day Off	Work Hours
John	Holiday	1/1/2021	0
John	Vacation	1/11/2021	8
John	Vacation	1/12/2021	8
Jane	Holiday	1/1/2021	0
Jane	Vacation	2/19/2021	8

And using power query I would like to get the following output:

Employee	January	February
John	16	0
Jane	0	8

where in each month column I get the total hours each employee has "booked" as vacation for that month.

I know that probably the internet is full of answers for my problem, but I don't know how to search for this.

Finally, as I said before, I am very very new to this, so if you could show me a step by step, that'd be awesome.

Thanks

[Fowmy]

@Anonymous 

Go to Data view, select the table, and click on New Column and add the following code:

Month Year = EOMONTH(Table13[Day Off],0)

Select the new column and apply the format as "mmm yyyy"

In Report View, Add Matrix Visual and place the fields as shown below.

[Anonymous]

thanks @Fowmy 

I am using Power Query in Excel. Maybe I'm in the wrong forum, but I thought I could post here because google searches always bring me here.

Is it possible to do this on power query in excel? If so, how? And if not, should I look into power bi?

[Fowmy · Accepted solution]

@Anonymous 

Yes, it is possible, got Power Query in Excel and follow these steps.
Create a blank Query, go to the Advanced Editor, clear the existing code, and paste the codes give below and follow the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs0tyMmvTE1V0lEKqSxIVchPU3BJrFTwT0sDiiBY4flF2Qoe+aVFxUqxOtFKXvkZeUBRj/yczJTESiDLUN9Q38jAyBDINEBWEZaYnFiSmZ8HUQJXY4FbjRGamsS8VAI2QVQgmWKkb2iJZEosAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Employee", type text}, {"Type of Day Off", type text}, {"Day Off", type date}, {"Work Hours", Int64.Type}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type1", "Month Name", each Date.MonthName([Day Off]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Day Off]), Int64.Type),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Inserted Year", {{"Year", type text}}, "en-US"),{"Month Name", "Year"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Month Year"),
    #"Grouped Rows" = Table.Group(#"Merged Columns", {"Employee", "Month Year"}, {{"Hours", each List.Sum([Work Hours]), type nullable number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Month Year"]), "Month Year", "Hours", List.Sum)
in
    #"Pivoted Column"

[Anonymous]

@Fowmy 

I have one follow-up question.

I noticed that the months do not appear in chronological order, like this 

or this 

how do I sort the columns chronologically?

[Fowmy]

@Anonymous 

Since you are using this in Excel I created two queries, one summary and the as a table so you can use a Pivot Table to Summary as you need. The month order is sorted out.

[Anonymous]

@Fowmy

It took me some trial and error, but I did it! Thanks a lot!!