Forum Discussion

carlbh's avatar
carlbh
Helper I
4 years ago
Solved

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,...
  • PaulDBrown's avatar
    4 years ago

    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