cancel
Showing results for
Did you mean:
Helper I

## Combining data from two tables.

I have a challenge I am trying to overcome and I am hoping someone here has had a similar issue and can assist.

I have a table containing all the cases managed by a helpdesk, date logged, duration, etc.

There are multiple cases for any given day, likewise there are days with no cases.

What I want to do is add the total duration of all cases handled in a given day in a seperate table that is a list of all days in year.

If there were no cases logged then I want the duration column in table 2 to show 0.

If there were cases then I want the duration column in table 2 to show total of duration of all cases logged that date from table 1.

I hope this makes sense. I am sure there is a way to do this, I just cant get my head round it. Fictional data to explain concept below.

 Table 1 Case Data Logged Date Time Case ID Duration (Mins) 01/09/2021 09:00 100 10 01/09/2021 11:15 101 20 01/09/2021 13:30 102 30 03/09/2021 08:45 103 10 03/09/2020 09:30 104 15 04/09/2021 10:15 105 20 06/09/2021 09:30 106 50 06/09/2021 11:00 107 5 08/09/2021 13:15 108 20 08/09/2021 14:00 109 20 10/09/2021 17:00 110 10

 Table 2 Total by Date Date Total Duration (mins) 01/09/2021 60 Sum of duration all cases logged on 1/9/21 e.g. Sum D4:D6 02/09/2021 0 Sum of duration all cases logged on 2/9/21 e.g.No cases on this day in case data table so returns 0 03/09/2021 25 Sum of duration all cases logged on 3/9/21 e.g.Sum of D7:D8 04/09/2021 20 Sum of duration all cases logged on 4/9/21 e.g.Sum of D9 05/09/2021 0 Sum of duration all cases logged on 5/9/21 e.g.No cases on this day in case data table so returns 0 06/09/2021 55 Sum of duration all cases logged on 6/9/21 e.g. Sum of D10:D11 07/09/2021 0 Sum of duration all cases logged on 7/9/21 e.g.No cases on this day in case data table so returns 0 08/09/2021 40 Sum of duration all cases logged on 8/9/21 e.g.Sum of D12:D13 09/09/2021 0 Sum of duration all cases logged on 9/9/21 e.g.No cases on this day in case data table so returns 0 10/09/2021 10 Sum of duration all cases logged on 10/9/21 e.g.Sum of D14
1 ACCEPTED SOLUTION
Super User

Here is one way of achieving what you are after.

First of all, you need to split the Logged Date Time into date and time columns. In Power Query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoAgDAS/Yjib0KWgwFcI//+GKCWt0cteJjtbaM0RPBUfKGCjUonc7iDp+v7iQEV6CEaGL+fKsxlGsnBWf65x9tn42e5LP96ZJo/qp7WfzP7x0z9Gpg8f98vLzptPnO35os9Gb3hc9aIcpPwUjvV9/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Logged Date Time" = _t, #"Case ID" = _t, #"Duration (Mins)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Logged Date Time", type datetime}, {"Case ID", Int64.Type}, {"Duration (Mins)", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Logged Date Time", type text}}, "es-ES"), "Logged Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Logged Date Time.1", "Logged Date Time.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Logged Date Time.1", type date}, {"Logged Date Time.2", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Logged Date Time.1", "Logged Date"}, {"Logged Date Time.2", "Logged Time"}})
in
#"Renamed Columns"``````

Which gets you this:

Nest create a Date Table using:

``````Date Table =
VAR MinD = MIN('Table'[Logged Date])
VAR MaxD = MAX('Table'[Logged Date])
RETURN
CALENDAR(MinD, MaxD)``````

Create a relationship between the Date Table and the Logged Date field. The model looks like this:

Create a Measure for the duration:

``Sum Duration = SUM('Table'[Duration (Mins)]) + 0``

And finally create the visual using the date field from the Date Table and the [Sum Duration] measure to get:

I've attached the sample PBIX file

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

2 REPLIES 2
Super User

Here is one way of achieving what you are after.

First of all, you need to split the Logged Date Time into date and time columns. In Power Query:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBBDoAgDAS/Yjib0KWgwFcI//+GKCWt0cteJjtbaM0RPBUfKGCjUonc7iDp+v7iQEV6CEaGL+fKsxlGsnBWf65x9tn42e5LP96ZJo/qp7WfzP7x0z9Gpg8f98vLzptPnO35os9Gb3hc9aIcpPwUjvV9/QI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Logged Date Time" = _t, #"Case ID" = _t, #"Duration (Mins)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Logged Date Time", type datetime}, {"Case ID", Int64.Type}, {"Duration (Mins)", Int64.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(Table.TransformColumnTypes(#"Changed Type", {{"Logged Date Time", type text}}, "es-ES"), "Logged Date Time", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Logged Date Time.1", "Logged Date Time.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Logged Date Time.1", type date}, {"Logged Date Time.2", type time}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Logged Date Time.1", "Logged Date"}, {"Logged Date Time.2", "Logged Time"}})
in
#"Renamed Columns"``````

Which gets you this:

Nest create a Date Table using:

``````Date Table =
VAR MinD = MIN('Table'[Logged Date])
VAR MaxD = MAX('Table'[Logged Date])
RETURN
CALENDAR(MinD, MaxD)``````

Create a relationship between the Date Table and the Logged Date field. The model looks like this:

Create a Measure for the duration:

``Sum Duration = SUM('Table'[Duration (Mins)]) + 0``

And finally create the visual using the date field from the Date Table and the [Sum Duration] measure to get:

I've attached the sample PBIX file

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Anonymous
Not applicable

Create calculated table of dates using CALENDAR or CALENDARAUTO, then add calculated column with sum of durations, something like this total_duration=CALCULATE(SUMX('Case Data','Case Data'[Duration (Mins)])).

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors