How to create a customer active table using log

[CSpina]

Hi, people.

I have a table with customer logs, where ID Cliente = "Customer ID", Start = "Ínicio" and End = "Término". 

I must organize the information in periods: 

Any clues? Thanks in advance.

[Syndicate_Admin]

Hello - here you are.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5BCsAgDATAr5SchSRrUmi/Iv7/G4YqHooKe8osZEshiAglUlaGQC/RVz0SN6ppurH6sRABY1Tsq+Dn99KtO3YDhuftAJsP8ulB5Fm6d/flgNoA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, DateTime1 = _t, DateTime2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"DateTime1", type datetime}, {"DateTime2", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client ID"}, {{"MaxStart", each List.Max([DateTime1]), type nullable datetime}, {"MaxEnd", each List.Max([DateTime2]), type nullable datetime}})
in
    #"Grouped Rows"

Start:

End

[jennratten]

Hello - here you are.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5BCsAgDATAr5SchSRrUmi/Iv7/G4YqHooKe8osZEshiAglUlaGQC/RVz0SN6ppurH6sRABY1Tsq+Dn99KtO3YDhuftAJsP8ulB5Fm6d/flgNoA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, DateTime1 = _t, DateTime2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"DateTime1", type datetime}, {"DateTime2", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client ID"}, {{"MaxStart", each List.Max([DateTime1]), type nullable datetime}, {"MaxEnd", each List.Max([DateTime2]), type nullable datetime}})
in
    #"Grouped Rows"

Start:

End

[CSpina]

Jenn, thanks.

Thanks, but I need the 2 results for customer 4000 as it has been active in 2 different periods. In my example 2851.

[jennratten · Accepted solution]

Sure thing - here you are.  There is the query + a custom function.

Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fc5BCsAgDATAr5SchSRrUmi/Iv7/G4YqHooKe8osZEshiAglUlaGQC/RVz0SN6ppurH6sRABY1Tsq+Dn99KtO3YDhuftAJsP8ulB5Fm6d/flgNoA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, DateTime1 = _t, DateTime2 = _t]),
    ChangeTypes = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"DateTime1", type datetime}, {"DateTime2", type datetime}}),
    Unpivot = Table.UnpivotOtherColumns(ChangeTypes, {"Client ID"}, "Attribute", "Value"),
    Sort = Table.Sort(Unpivot,{{"Client ID", Order.Ascending}, {"Value", Order.Ascending}}),
    fnResult = fnTable_ReferenceDifferentRow(Sort, 1, {"Value"}, null, ".next", null),
    Filter = Table.SelectRows(fnResult, each ([Attribute] = "DateTime1")),
    RemoveColumn = Table.RemoveColumns(Filter,{"Attribute"})
in
    RemoveColumn

Custom Function: fnTable_ReferenceDifferentRow

let
  Source = let
    fnTable_ReferenceDifferentRow = (Table as table, optional Step as number, optional SelectedColumns, optional GroupByColumns, optional Suffix as text, optional Buffer as any) =>
    // Source: https://www.thebiccountant.com/2018/07/12/fast-and-easy-way-to-reference-previous-or-next-rows-in-power-query-or-power-bi/
    // The only mandatory parameter is your table and then it will return a table with the previous rows values of all columns. So fnTable_ReferenceDifferentRow(MyTable) will return the result from above.
    // The default-value for this parameter is set to -1 to return the previous row if you omit it. If you want the values from the next row instead, fill in 1. 2 will return the overnext and -2 the pre-previous row.
    // You probably just need one or a few columns/fields from the previous row: In the 3rd parameter you can enter a list of column names to be returned:   fnTable_ReferenceDifferentRow(MyTable, null, {"Value"})
    // Quite often the previous values shall only be returned within a group of rows. (That’s when you use [MyColumn] = EARLIER([MyColumn]) in DAX). You can enter a list of group-columns in the 4th parameter of this function: fnTable_ReferenceDifferentRow(MyTable, null, null, {"Product"})
    // By default, the suffix “.Prev” will be added to the new column names. Use the 5th parameter to change if needed. In this example, I reference the row below using “1” for the 2nd parameter: fnTable_ReferenceDifferentRow(MyTable, 1, null, null, "Next")
    // If performance is still too bad, you can try to buffer the input table. Any value in the 6th parameter will do that for you (although I haven’t seen a performance improvement for my test cases).
    let
    // Steps to prepare the (optional) parameters for the nested function "fnFetchNextRow"
    Source = if Buffer = null then Table else Table.Buffer(Table),
    Step0 = if Step = null then -1 else Step,
    Step_ = if Step = null then 1 else Number.Abs(Step),
    Suffix = if Suffix = null then ".Prev" else Suffix,
    GroupByColumns = if GroupByColumns = null then null else GroupByColumns,
    ShiftFunction = if Step0 < 0 then Table.RemoveLastN else Table.RemoveFirstN,
    ColNames = List.Buffer(Table.ColumnNames(Source)),
    NewColNames = if SelectedColumns = null then ColNames else SelectedColumns,
    CountNewCols = List.Count(NewColNames),
    // Core function that retrieves values from previous or next rows (depending on sign of parameter "Step")
    fnFetchNextRow = (Table_ as table, optional Step as number, optional SelectedColumns, optional Suffix as text, optional Buffer as any) =>
    let
    MergeTable = if SelectedColumns = null then Table_ else Table.SelectColumns(Table_, SelectedColumns),
    Shift = if Step0 > 0 then ShiftFunction(MergeTable, Step_) & #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_))
    else #table(NewColNames, List.Repeat({List.Repeat({null}, CountNewCols)}, Step_)) & ShiftFunction(MergeTable, Step_),
    Reassemble = Table.ToColumns(Table_) & Table.ToColumns(Shift),
    Custom1 = Table.FromColumns( Reassemble,  Table.ColumnNames(Source) & List.Transform(NewColNames, each _&Suffix ) )
in
    Custom1,
    // optional grouping on certain columns
    #"Grouped Rows" = Table.Group(Source, GroupByColumns, {{"All", each _}}, GroupKind.Local),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each fnFetchNextRow([All], Step0, SelectedColumns, Suffix, Buffer)),
    #"Removed Columns" = Table.Combine(Table.RemoveColumns(#"Added Custom", GroupByColumns & {"All"})[Custom]),
    // case no grouping
    NoGroup = fnFetchNextRow(Source, Step0, SelectedColumns, Suffix, Buffer),
    // select case grouping
    Result = if GroupByColumns = null then NoGroup else #"Removed Columns"
in
    Result,
    documentation = [
    Documentation.Name =  " Table.ReferenceDifferentRow ",
    Documentation.Description = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
    Documentation.LongDescription = " Adds columns to a <code>Table</code> with values from previous or next rows (according to the <code>Step</code>-index in the 2nd parameter) ",
    Documentation.Category = " Table ",
    Documentation.Source = "  ",
    Documentation.Version = " 1.0 ",
    Documentation.Author = " Imke Feldmann (www.TheBIccountant.com ) ",
    Documentation.Examples = {[Description =  "  ",
    Code = " fnTable_ReferenceDifferentRow( #table( {""Product"", ""Value""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""} } ) ) ) ",
    Result = " #table( {""Product"", ""Value"", ""Product.Prev"", ""Value.Prev""}, List.Zip( { {""A"" ,""A"" ,""B"" ,""B"" ,""B""}, {""1"" ,""2"" ,""3"" ,""4"" ,""5""}, {null ,""A"" ,""A"" ,""B"" ,""B""}, {null ,""1"" ,""2"" ,""3"" ,""4""} } ) ) "]}]
in
    Value.ReplaceType(fnTable_ReferenceDifferentRow, Value.ReplaceMetadata(Value.Type(fnTable_ReferenceDifferentRow), documentation))
in
  Source

Result:

[CSpina]

@jennratten Thank Very Much!

[jennratten]

You're welcome!