Forum Discussion
Combining data from two tables.
- 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)]) + 0And 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
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