Separating Transactions from Dates in single column

J

jjsararas

New member
Joined
Sep 25, 2019
Messages
12
Reaction score
0
Points
1
Excel Version(s)
2016, 365
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?
 
Can you attach the workbook so we can see the current layout properly?
 
file View attachment 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"
 
Thanks so much horseyride, that does the trick. I need to drill it into my head that index columns are like a swiss army knife..
 
I'm good to go but thank you Ali!
 
You must log in or register to reply here.
Back
Top