More Efficient Way to Replace Values

[borkybork]

Hi,

Is there a more efficient way to replace "Cash" with "Other Income"? I created a Group By Journal ID to identify which Journal IDs have an "Other Income" row and replaced GL Name with GroupBy.JournalID if GroupBy.JournalID is not null.

Journal ID	Memo	GL Name	Debit/(Credit)	GroupBy.JournalID
123	Income	Cash	100	Other Income
123	Income	Other Income	(100)	Other Income
124	Income	Cash	200	Other Income
124	Income	Other Income	(200)	Other Income
125	Accruals	Expense	100	null
125	Accruals	Expense-Tax	200	null
125	Accruals	Expense-Rent	100	null
125	Accruals	Expense-Bookkeeping	350	null
125	Accruals	Expense-Supplies	100	null
125	Accruals	Liability-Professional fees	(350)	null
125	Accruals	Liability-Other	(500)	null

GroupBy Journal ID - Grouped by Journal ID and filtered for "Other Income". I then merged this query with the main one and added "GroupBy.JournalID" column .

Journal ID	GL Name
123	Other Income
124	Other Income

I also need to replace the journal ID if it's a 1:1 entry in journals with multiple rows and add a suffix at the end, ie Journal ID 125-1.

This was my long process: I created 3 columns debit (positive values), credit (negative values) and absolute values of Debit/(Credit). Then Grouped by, merged that with the main report and added GroupBy.Credit column, then added 1:1 entry column to check if there's a match (Absolute Values = GroupBy.Credit). Then added yet another column New Journal ID to add the suffix if True.

Is there a way to achieve this with less queries and less steps? I think it's slowing down my file a lot, and it takes longer to refresh my report, sometimes it crashes if it took too long Any help would be greatly appreciated! Thanks so much in advance!!

Journal ID	Memo	GL Name	Debit/(Credit)	Absolute Values	Debit	Credit	GroupBy.Credit	1:1 entry	New Journal ID
125	Accruals	Expense	100	100	100	0	null	False	125
125	Accruals	Expense-Tax	200	200	200	0	null	False	125
125	Accruals	Expense-Rent	100	100	100	0	null	False	125
125	Accruals	Expense-Bookkeeping	350	350	350	0	350	True	125-1
125	Accruals	Expense-Supplies	100	100	100	0	null	False	125
125	Accruals	Liability-Professional fees	(350)	350	0	350	350	True	125-1
125	Accruals	Liability-Other	(500)	500	0	500	null	False	125

Group By Journal ID, Memo and Credit, then filtered out the zeros

Journal ID	Memo	Credit
125	Accruals	350
125	Accruals	500

 

[Sam_D_Ben]

Something like this,

Book1
	A	B	C	D
1	Journal ID	Memo	GL Name	Debit/(Credit)
2	123	Income	Other Income	100
3	123-CR	Income	Other Income	-100
4	124	Income	Other Income	200
5	124-CR	Income	Other Income	-200
6	125	Accruals	Expense	100
7	125	Accruals	Expense-Tax	200
8	125	Accruals	Expense-Rent	100
9	125	Accruals	Expense-Bookkeeping	350
10	125	Accruals	Expense-Supplies	100
11	125-CR	Accruals	Liability-Professional fees	-350
12	125-CR	Accruals	Liability-Other	-500
13
14	123-CR	Income	Other Income	-100
15	124-CR	Income	Other Income	-200
16	125-CR	Accruals	Liability-Professional fees	-350
17	125-CR	Accruals	Liability-Other	-500
Sheet1

Sub ModifyJournalEntries()
    Dim ws As Worksheet, lastRow As Long, i As Long, newRow As Long
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    newRow = lastRow + 2
    
    For i = 2 To lastRow
            If ws.Cells(i, 4).Value < 0 Then
            ws.Cells(i, 1).Value = ws.Cells(i, 1).Value & "-CR"
            ws.Rows(i).Copy
            ws.Rows(newRow).PasteSpecial xlPasteValues
            newRow = newRow + 1
        End If
        
        ws.Cells(i, 3).Value = IIf(ws.Cells(i, 3).Value = "Cash", "Other Income", ws.Cells(i, 3).Value)
    Next i
    
    Application.CutCopyMode = False
    MsgBox "Journal entries modified successfully!", vbInformation
End Sub

 

[GraH]

Something like this,

Book1
	A	B	C	D
1	Journal ID	Memo	GL Name	Debit/(Credit)
2	123	Income	Other Income	100
3	123-CR	Income	Other Income	-100
4	124	Income	Other Income	200
5	124-CR	Income	Other Income	-200
6	125	Accruals	Expense	100
7	125	Accruals	Expense-Tax	200
8	125	Accruals	Expense-Rent	100
9	125	Accruals	Expense-Bookkeeping	350
10	125	Accruals	Expense-Supplies	100
11	125-CR	Accruals	Liability-Professional fees	-350
12	125-CR	Accruals	Liability-Other	-500
13
14	123-CR	Income	Other Income	-100
15	124-CR	Income	Other Income	-200
16	125-CR	Accruals	Liability-Professional fees	-350
17	125-CR	Accruals	Liability-Other	-500
Sheet1

Sub ModifyJournalEntries()
    Dim ws As Worksheet, lastRow As Long, i As Long, newRow As Long
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    newRow = lastRow + 2
   
    For i = 2 To lastRow
            If ws.Cells(i, 4).Value < 0 Then
            ws.Cells(i, 1).Value = ws.Cells(i, 1).Value & "-CR"
            ws.Rows(i).Copy
            ws.Rows(newRow).PasteSpecial xlPasteValues
            newRow = newRow + 1
        End If
       
        ws.Cells(i, 3).Value = IIf(ws.Cells(i, 3).Value = "Cash", "Other Income", ws.Cells(i, 3).Value)
    Next i
   
    Application.CutCopyMode = False
    MsgBox "Journal entries modified successfully!", vbInformation
End Sub

I think the question was how to accomplish this with Power Query, not VBA.

 

[GraH]

Not sure I entirely get it, but this is my first attempt. Going with the provided sample, I might have over simplified.

Journal ID	Memo	GL Name	Debit/(Credit)
123	Income	Cash	100
123	Income	Other Income	-100
124	Income	Cash	200
124	Income	Other Income	-200
125	Accruals	Expense	100
125	Accruals	Expense-Tax	200
125	Accruals	Expense-Rent	100
125	Accruals	Expense-Bookkeeping	350
125	Accruals	Expense-Supplies	100
125	Accruals	Liability-Professional fees	-350
125	Accruals	Liability-Other	-500

Why the replacement of "cash" is needed, with the required result is a mystery to me, but I left it in.

let
    Source = Excel.CurrentWorkbook(){[Name="sample"]}[Content],
    Auto_Set_Types = Table.TransformColumnTypes(Source,{{"Journal ID", type text}, {"Memo", type text}, {"GL Name", type text}, {"Debit/(Credit)", Int64.Type}}),
    Replace_Cash = Table.ReplaceValue(Auto_Set_Types,"Cash","Other Income",Replacer.ReplaceText,{"GL Name"}),
    Temp_d_c = Table.AddColumn(Replace_Cash, "Custom", each if [#"Debit/(Credit)"] < 0 then "credit" else "debit"),
    Pivot_d_c = Table.Pivot(Temp_d_c, List.Distinct(Temp_d_c[Custom]), "Custom", "Debit/(Credit)"),
    c_as_abs_val = Table.TransformColumns(Pivot_d_c,{{"credit", Number.Abs, Int64.Type}}),
    New_id = Table.ReplaceValue(c_as_abs_val,each [Journal ID],each if [debit]=null then [Journal ID]& "-1" else [Journal ID],Replacer.ReplaceValue,{"Journal ID"}),
    Filter_on_id = Table.SelectRows(New_id, each ([Journal ID] = "125-1")),
    Keep_cols = Table.RemoveColumns(Filter_on_id,{"GL Name", "debit"})
in
    Keep_cols