Results 1 to 3 of 3

Thread: Separating Transactions from Dates in single column

  1. #1
    Seeker jjsararas's Avatar
    Join Date
    Sep 2019
    Posts
    10
    Articles
    0
    Excel Version
    2016, 365

    Separating Transactions from Dates in single column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I thought this would/should be easy but I'm scratching my head. I've been given a list like:

    June 1st
    Gas $50
    Dinner $75
    Dress $90

    June 3rd
    Book $25
    Shoes $65

    June 5
    Starbucks $18
    Gas $45

    ... and so on. How do I get the transactions next to the dates in PQ?

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,280
    Articles
    0
    Excel Version
    Office 365 Subscription
    Can you attach the workbook so we can see the current layout properly?
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    94
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    file shifty.xlsx

    If you need to preserve blank rows

    Highlight data and use Data...From Table/Range ... [ ] header rows unchecked
    Add column...add index....
    Add column .. custom column ... named Custom with formula
    if [Index]=0 or #"Added Index"{[Index]-1}[Column1]=null then [Column1] else null
    this checks to see if row above current row is blank, and if so, then copy over the date field
    Add column ... custom column ... named Custom.1 with formula
    if [Custom]=null then 1 else 0
    this allows us to filter out the date row after we fill it down
    Right Click column Custom and Fill...Down...
    Add column ... custom column ... named Custom.2 with formula
    if [Column1]=null then [Column1] else [Custom]
    That step removes the data field when original row was a blank/null
    Use drop box next to Custom.1 column title and unselect the [x]0
    Click to select Index, Custom, Custom.1 columns and right-click "remove columns"
    File ... close and Load ... to table

    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 or #"Added Index"{[Index]-1}[Column1]=null then [Column1] else null),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom]=null then 1 else 0),
        #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
        #"Added Custom2" = Table.AddColumn(#"Filled Down", "Custom.2", each if [Column1]=null then [Column1] else [Custom]),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom.1] = 1)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom", "Custom.1"})
    in
        #"Removed Columns"
    if you do not need to preserve blank rows, similar but fewer steps

    Code:
    let  Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [Index]=0 or #"Added Index"{[Index]-1}[Column1]=null then [Column1] else null),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Column1]= null then 0 else if [Custom]=null then 1 else 0),
        #"Filled Down" = Table.FillDown(#"Added Custom1",{"Custom"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Custom.1] = 1)),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom.1"})
    in
        #"Removed Columns"

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •