track where the balance is coming from

[BORUCH]

hi all i have the below table

AM Balance Order Jul.08.24 (Part 3).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	ITEM #	AM SNACKS	ORIGINAL BALANCE DATE	ORIGINAL BALANCE	ORDER DATE	QTY ORDERED	BALANCE	RECEIVED DATE	INVOICE #	QTY RECEIVED	BALANCE	RECEIVED DATE	INVOICE #	QTY RECEIVED	BALANCE
2	00001	apples	9/6/2023	8	11/2/2023	75	83	11/19/2023	9999	10	73	11/30/2023	123	50	23
3	00002	oranges	9/6/2023	-34	11/2/2023	500	466	11/19/2023	8888	400	66	11/30/2023	456	60	6
Sheet1

Cell Formulas
Range	Formula
G2:G3	G2	=D2+F2
K2:K3,O2:O3	K2	=G2-J2

i would like an excel formula on another sheet for each item to track the balance by date

 

[Alex Blakenburg]

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 ?

 

[Alex Blakenburg]

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 ?

 

[BORUCH]

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

 

[Alex Blakenburg]

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?

 

[BORUCH]

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?

Not really

 

[Alex Blakenburg]

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.

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"

 

[BORUCH]

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.

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..

 

[BORUCH]

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

 

[Alex Blakenburg]

Sorry my Date logic used Ends With and needs to be Contains, try this:

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"