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

 

[BORUCH]

Any updates to my question

 

[StephenCrump]

Your question is not clear, which is probably why you have had no replies.

What does "another sheet" look like, and for the data you have posted, what results do you expect to see?

 

[BORUCH]

Your question is not clear, which is probably why you have had no replies.

What does "another sheet" look like, and for the data you have posted, what results do you expect to see?

Ok so for example on another sheet I would look up item number and return by date the sum of all balances

 

[StephenCrump]

... return by date the sum of all balances ...

Bear in mind we know nothing about your workbook other than what you post here.

Based on this description, I can't even guess what you are trying to do.

Can you please show us what "another sheet" looks like, preferably for more Sheet1 data than you have already posted.

 

[BORUCH]

Bear in mind we know nothing about your workbook other than what you post here.

Based on this description, I can't even guess what you are trying to do.

Can you please show us what "another sheet" looks like, preferably for more Sheet1 data than you have already posted.

hi let me revise my question how can i unpivot this to look like a reg table vs same column names repeating over and over

 

[Alex Blakenburg]

Are you looking for the output to look something like this ?

20241112 PQ Power Query Unpivot messy BORUCH.xlsm
	A	B	C	D	E	F	G
1	ITEM #	AM SNACKS	Date	InvoiceNo	Type	Qty	Balance
2	1	apples	6/09/2023		ORIGINAL BALANCE		8
3	1	apples	2/11/2023		ORDERED	75	83
4	1	apples	19/11/2023	9999	RECEIVED	-10	73
5	1	apples	19/11/2023	123	RECEIVED	-50	23
6	2	oranges	6/09/2023	123	ORIGINAL BALANCE		-34
7	2	oranges	2/11/2023	123	ORDERED	500	466
8	2	oranges	19/11/2023	8888	RECEIVED	-400	66
9	2	oranges	19/11/2023	456	RECEIVED	-60	6
Unpivot

I did this using mostly just the UI in Power Query (which I see you have used in the past).
The resulting code is below, I am sure that someone using M can do it in much less code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"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]

Are you looking for the output to look something like this ?

20241112 PQ Power Query Unpivot messy BORUCH.xlsm
	A	B	C	D	E	F	G
1	ITEM #	AM SNACKS	Date	InvoiceNo	Type	Qty	Balance
2	1	apples	6/09/2023		ORIGINAL BALANCE		8
3	1	apples	2/11/2023		ORDERED	75	83
4	1	apples	19/11/2023	9999	RECEIVED	-10	73
5	1	apples	19/11/2023	123	RECEIVED	-50	23
6	2	oranges	6/09/2023	123	ORIGINAL BALANCE		-34
7	2	oranges	2/11/2023	123	ORDERED	500	466
8	2	oranges	19/11/2023	8888	RECEIVED	-400	66
9	2	oranges	19/11/2023	456	RECEIVED	-60	6
Unpivot

I did this using mostly just the UI in Power Query (which I see you have used in the past).
The resulting code is below, I am sure that someone using M can do it in much less code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"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
yes that's basically it
I would like one more change, that if lets say column J cell J2, in my original example, if its blank, i still want to show that in my final table as another row ,with type received zero with the balance whatever is in the net column cell which in my case would still be 83.

 

[Alex Blakenburg]

What would be in columns H & I when J is blank ?

 

[BORUCH]

What would be in columns H & I when J is blank ?

the same info that's there ,its one big receiving with one receiving date, we just happen to have received the oranges and not the apples
and actually the invoice numbers in my case are also the same there was no reason why i put different invoice numbers since it was one receiving but that point is irrelevant for what i want