locked
Unique ID RRS feed

  • Question

  • Currently have a data set where I need to calculate the duration of time each individual spends in a shop. I have a table that has the following:

    Credit card number (last 4 digits), Movement (i.e. entry or exit) and date/time.

    In order to calculate duration I need to pivot the movement column like so:

    credit card number. entry.exit

    1234.09/03/2020 09:00.09/03/2019 10:00

    But because the credit card number is unique to an individual and not the event.

    Monday, March 9, 2020 12:48 AM

Answers

  • You can also put in place function fxPrepMoveTable with code:

    (inTable as table, moveType as text) as table =>
    let
        Source = inTable,
        Selected = Table.SelectRows(Source, each [Move] = moveType),
        Grouped = Table.Group(Selected, {"CC num"},
            {"GRP", each
                Table.AddIndexColumn(
                    Table.RemoveColumns(_,{"CC num","Move"}),
                    "Idx", 0, 1
                ),
                type table [Idx=Int64.Type, DateTime=datetime]
            }
        ),
        Expanded = Table.ExpandTableColumn(Grouped, "GRP", {"Idx", "DateTime"})
    in
        Table.RenameColumns(Expanded, {{"DateTime",moveType&"DateTime"}})

    Then your main query code is:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,
            {{"CC num", Int64.Type}, {"Move", type text}, {"DateTime", type datetime}}
        ),
        TableEntries = fxPrepMoveTable(ChangedTypes, "Entry"),
        TableExits = fxPrepMoveTable(ChangedTypes, "Exit"),
        JoinedTablesOnCcAndIndex = Table.NestedJoin(
            TableEntries,{"CC num","Idx"}, TableExits,{"CC num","Idx"}, "ExitData", JoinKind.LeftOuter
        ),
        ExpandedExitDateTime = Table.ExpandTableColumn(JoinedTablesOnCcAndIndex, "ExitData", {"ExitDateTime"}),
        RemovedIdx = Table.RemoveColumns(ExpandedExitDateTime,{"Idx"})
    in
        RemovedIdx

     

    Tuesday, March 10, 2020 8:40 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With cccc.mm/dd/yyyy hh:mm.mm/dd/yyyy hh:mm type input.
    Sum shop times by day and week.
    With PivotChart and Slicers.
    http://www.mediafire.com/file/1qsgbcib51l93cd/03_11_20.xlsx/file
    http://www.mediafire.com/file/jmuvctcnu4mupz5/03_11_20.pdf/file

    Wednesday, March 11, 2020 5:08 PM

All replies

  • Hi

    From your problem description that isn't quite clear (to me at least) not sure the following is really what you expect to do:

    Query code (assuming your [DateTime] is ordered):

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,
            {{"CC num", Int64.Type}, {"Move", type text}, {"DateTime", type datetime}}
        ),
    
        Entries = Table.SelectRows(ChangedTypes, each [Move] = "Entry"),
        GroupedEntries = Table.Group(Entries, {"CC num"},
            {
                {"CcEntry", each
                    let
                        removedColumns = Table.RemoveColumns(_,{"CC num","Move"}),
                        renamedDateTime = Table.RenameColumns(removedColumns, {{"DateTime","EntryDatetime"}})
                    in
                        Table.AddIndexColumn(renamedDateTime, "IdxEntry", 0, 1),
                type table [IdxEntry=Int64.Type, EntryDatetime=datetime]}
            }
        ),
        TableEntries = Table.ExpandTableColumn(GroupedEntries, "CcEntry", {"IdxEntry", "EntryDatetime"}),
    
        Exits = Table.SelectRows(ChangedTypes, each [Move] = "Exit"),
        GroupedExits = Table.Group(Exits, {"CC num"},
            {
                {"CcExit", each
                    let
                        removedColumns = Table.RemoveColumns(_,{"CC num","Move"}),
                        renamedDateTime = Table.RenameColumns(removedColumns, {{"DateTime","ExitDatetime"}})
                    in
                        Table.AddIndexColumn(renamedDateTime, "IdxExit", 0, 1),
                type table [IdxExit=Int64.Type, ExitDatetime=datetime]}
            }
        ),
        TableExits = Table.ExpandTableColumn(GroupedExits, "CcExit", {"IdxExit", "ExitDatetime"}),
        JoinedTablesOnCcAndIndex = Table.NestedJoin(
            TableEntries,{"CC num","IdxEntry"}, TableExits,{"CC num","IdxExit"}, "ExitData", JoinKind.LeftOuter
        ),
        ExpandedExitDateTime = Table.ExpandTableColumn(JoinedTablesOnCcAndIndex, "ExitData", {"ExitDatetime"}),
        RemovedIdxEntry = Table.RemoveColumns(ExpandedExitDateTime,{"IdxEntry"})
    in
        RemovedIdxEntry

    Corresponding workbook avail here

    Nice day...

    Tuesday, March 10, 2020 7:37 AM
  • You can also put in place function fxPrepMoveTable with code:

    (inTable as table, moveType as text) as table =>
    let
        Source = inTable,
        Selected = Table.SelectRows(Source, each [Move] = moveType),
        Grouped = Table.Group(Selected, {"CC num"},
            {"GRP", each
                Table.AddIndexColumn(
                    Table.RemoveColumns(_,{"CC num","Move"}),
                    "Idx", 0, 1
                ),
                type table [Idx=Int64.Type, DateTime=datetime]
            }
        ),
        Expanded = Table.ExpandTableColumn(Grouped, "GRP", {"Idx", "DateTime"})
    in
        Table.RenameColumns(Expanded, {{"DateTime",moveType&"DateTime"}})

    Then your main query code is:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedTypes = Table.TransformColumnTypes(Source,
            {{"CC num", Int64.Type}, {"Move", type text}, {"DateTime", type datetime}}
        ),
        TableEntries = fxPrepMoveTable(ChangedTypes, "Entry"),
        TableExits = fxPrepMoveTable(ChangedTypes, "Exit"),
        JoinedTablesOnCcAndIndex = Table.NestedJoin(
            TableEntries,{"CC num","Idx"}, TableExits,{"CC num","Idx"}, "ExitData", JoinKind.LeftOuter
        ),
        ExpandedExitDateTime = Table.ExpandTableColumn(JoinedTablesOnCcAndIndex, "ExitData", {"ExitDateTime"}),
        RemovedIdx = Table.RemoveColumns(ExpandedExitDateTime,{"Idx"})
    in
        RemovedIdx

     

    Tuesday, March 10, 2020 8:40 AM
  • Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With cccc.mm/dd/yyyy hh:mm.mm/dd/yyyy hh:mm type input.
    Sum shop times by day and week.
    With PivotChart and Slicers.
    http://www.mediafire.com/file/1qsgbcib51l93cd/03_11_20.xlsx/file
    http://www.mediafire.com/file/jmuvctcnu4mupz5/03_11_20.pdf/file

    Wednesday, March 11, 2020 5:08 PM