Combining data from two columns with different dates

[Cenex_Chris]

I am relatively new to PowerBI, so apologies if there is a simple solution I'm missing here. I have weekly timesheets created in a canvas app within Project for the Web. Each timesheet has a start date, then the number of hours per project per day (using just "Mon", "Tue", "Wed" etc.)

I'm trying to create monthly summaries of the data, but this is challenging because a week might start in one month and end in another. I've managed to set up columns to calculate what falls where, but it means that I basically have columns as follows:

Person	Project	Month 1	Month 1 Hours	Month 2	Month 2 hours
Person A	Project 1	Jan-24	15	Feb-24	5
Person A	Project 2	Jan-24	3	Feb-24	0
Person A	Project 3	Feb-24	10	Mar-24	1
Person B	Project 2	Jan-24	30	Feb-24	0
Person C	Project 1	Feb-24	21	Mar-24	7
Person C	Project 3	Jan-24	5	Feb-24	2
Person C	Project 4	Feb-24	1	Mar-24	3

I want to consolidate the hours across the months, but without losing the person & project breakdown. In other words, I want to see the total hours booked to a project within a month by each individual.

Any suggestions of how I can do this?

I've tried a few different approaches, but I'm quite new to PowerBI and this has got me stumped. If I use the Month as the X-axis on a graph and then Month 1 hours and Month 2 hours as the Y-axis then I end up with Month 2 hours being associated to Month 1, which isn't right (e.g. for row 1 above, this would give 20 in Jan, rather than 15 hours in Jan and 5 in Feb.

Any help or suggestions would be amazing! Thanks 🙂

[v-junyant-msft · Accepted solution]

Hi @Cenex_Chris ,

Your Month1 and Month2 are distributed in two columns, which is inconvenient when you create visual objects. Since this is the Power Query forum, I suggest you use the following M code in Power Query to transform your data:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtKs7PU3BU0lEKKMrPSk0uUTAEsr0S83SNTIAMQ1Mg4ZaaBOGZKsXqYNVjhKzHGFmLAS4tKKoMDYCEb2IRlIesxwmnNQY47XFG8w5clZEhsj3muPQYI9uDEgJGuLSYoHgH2RZjpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Project = _t, #"Month 1" = _t, #"Month 1 Hours" = _t, #"Month 2" = _t, #"Month 2 hours" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Month 1", type date}, {"Month 2", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Month 1 Hours", Int64.Type}, {"Month 2 hours", Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Changed Type", {{"Month 1", type text}, {"Month 1 Hours", type text}}, "en-US"),{"Month 1", "Month 1 Hours"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Month 2", type text}, {"Month 2 hours", type text}}, "en-US"),{"Month 2", "Month 2 hours"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged.1"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {"Person", "Project"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.1", "Value.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value.1", type date}, {"Value.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value.1", "Month"}, {"Value.2", "Value"}})
in
    #"Renamed Columns"

Just create a blank query and put all of the function into the Advanced Editor:

And after transformation:

Then click "Close and Apply":

Then you can create the visual, here I will give you an example:

Or like this:

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