cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
beatrizroque
Regular Visitor

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

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:

EmployeeType of Day OffDay OffWork Hours
JohnHoliday1/1/20210
JohnVacation1/11/20218
JohnVacation1/12/20218
JaneHoliday1/1/20210
JaneVacation2/19/20218

 

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

EmployeeJanuaryFebruary
John160
Jane08

 

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

1 ACCEPTED SOLUTION

@beatrizroque 

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"

Fowmy_0-1623422863205.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

6 REPLIES 6
Fowmy
Super User
Super User

@beatrizroque 

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"

Fowmy_0-1623421276440.png
In Report View, Add Matrix Visual and place the fields as shown below.

Fowmy_1-1623421330940.png

 

 

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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?

@beatrizroque 

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"

Fowmy_0-1623422863205.png

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

@Fowmy 

I have one follow-up question.

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

beatrizroque_0-1623429464471.png

or this 

beatrizroque_1-1623429484612.png


how do I sort the columns chronologically?

@beatrizroque 

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.



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmy

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

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Kudoed Authors