track where the balance is coming from

BORUCH

Well-known Member
Joined
Mar 1, 2016
Messages
548
Office Version
  1. 365
Platform
  1. Windows
hi all i have the below table
AM Balance Order Jul.08.24 (Part 3).xlsx
ABCDEFGHIJKLMNO
1ITEM #AM SNACKSORIGINAL BALANCE DATEORIGINAL BALANCEORDER DATE QTY ORDEREDBALANCERECEIVED DATEINVOICE #QTY RECEIVEDBALANCERECEIVED DATEINVOICE #QTY RECEIVEDBALANCE
200001apples9/6/2023811/2/2023758311/19/20239999107311/30/20231235023
300002oranges9/6/2023-3411/2/202350046611/19/202388884006611/30/2023456606
Sheet1
Cell Formulas
RangeFormula
G2:G3G2=D2+F2
K2:K3,O2:O3K2=G2-J2


i would like an excel formula on another sheet for each item to track the balance by date
 
I am guessing that the number of columns is variable ? It this correct ? Is there an upper limit to the number of columns ?
Is using VBA an option if I can't resolve it in PQ ?
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
The easiest is to convert the nulls to 0 in PQ for Column J before doing the unpivot BUT you have another received column N, do we convert all the nulls in that column to 0 as well.
Then since we already have 2 received columns will there be more ?
 
Upvote 0
The easiest is to convert the nulls to 0 in PQ for Column J before doing the unpivot BUT you have another received column N, do we convert all the nulls in that column to 0 as well.
Then since we already have 2 received columns will there be more ?
there can be more columns i would prefer the power query option and yes the number of columns is variable and they can be zero in column n to
 
Upvote 0
It would solve a lot of issues if you populate the columns with 0 when you put in the Invoice Date and Invoice No. Is that practicable?
 
Upvote 0
OK See it this works for you.
The first line currently refers to Table1, so change that to your table name.
The change from the previous code is the addition of the 2nd and 3rd line.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.EndsWith([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
 
Last edited:
Upvote 0
OK See it this works for you.
The first line currently refers to Table1, so change that to your table name.
The change from the previous code is the addition of the 2nd and 3rd line.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.EndsWith([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
hi
thanks this basically does it one problem is that the date column is always picking up the receiving date its suppose to show me the date corresponding to the type, Ordered is order date etc..
 
Upvote 0
so in the above example if i would to change the Receiving date in column L to 12/29/2024 it wouldn't pick it up in the table
 
Upvote 0
Sorry my Date logic used Ends With and needs to be Contains, try this:
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
 
Upvote 0
Sorry my Date logic used Ends With and needs to be Contains, try this:
Rich (BB code):
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Qty_Recvd_Columns = List.Select(Table.ColumnNames(Source), each Text.Contains(_,"QTY RECEIVED",Comparer.OrdinalIgnoreCase)),
    Qty_NullToZero = Table.ReplaceValue(Source,null,0,Replacer.ReplaceValue,Qty_Recvd_Columns),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Qty_NullToZero, {"ITEM #", "AM SNACKS"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1, Int64.Type),
    AddedCondColDate = Table.AddColumn(#"Added Index", "Date", each if Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColInvNo = Table.AddColumn(AddedCondColDate, "InvoiceNo", each if Text.StartsWith([Attribute], "INVOICE") then [Value] else null),
    AddedCondColBalance = Table.AddColumn(AddedCondColInvNo, "Balance", each if Text.Contains([Attribute], "BALANCE") and not Text.Contains([Attribute], "DATE") then [Value] else null),
    AddedCondColType = Table.AddColumn(AddedCondColBalance, "Type", each if Text.Contains([Attribute], "QTY") then [Attribute] else if [Attribute] = "ORIGINAL BALANCE" then [Attribute] else null),
    ReplacedValueQTY = Table.ReplaceValue(AddedCondColType,"QTY ","",Replacer.ReplaceText,{"Type"}),
    #"Split Column by Character Transition" = Table.SplitColumn(ReplacedValueQTY, "Type", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Type.1", "Type.2"}),
    RemovedColumnsTypeNo = Table.RemoveColumns(#"Split Column by Character Transition",{"Type.2"}),
    RenamedColumnsType = Table.RenameColumns(RemovedColumnsTypeNo,{{"Type.1", "Type"}}),
    AddedCondColQty = Table.AddColumn(RenamedColumnsType, "Qty", each if [Type] = "RECEIVED" then -1 * [Value] else if Text.StartsWith([Attribute], "QTY") then [Value] else null),
    #"Filled Down" = Table.FillDown(AddedCondColQty,{"Date", "InvoiceNo"}),
    FilledUpBalance = Table.FillUp(#"Filled Down",{"Balance"}),
    FilteredRowsNullType = Table.SelectRows(FilledUpBalance, each [Type] <> null and [Type] <> ""),
    #"Removed Columns" = Table.RemoveColumns(FilteredRowsNullType,{"Attribute", "Value", "Index"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ITEM #", "AM SNACKS", "Date", "InvoiceNo", "Type", "Qty", "Balance"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"ITEM #", Int64.Type}, {"AM SNACKS", type text},  {"Date", type date}, {"InvoiceNo", Int64.Type}, {"Qty", Int64.Type}, {"Type", type text}, {"Balance", Int64.Type}})
in
    #"Changed Type"
thanks so much this worked perfectly

i just copied the logic you did by date to invoice #
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top