The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
In Power query, i have a large database of transactions in the format as below
Date | Item Code | In Qty | Out Qty |
1/12/2024 | A | 100 | |
1/14/2024 | A | 50 | |
1/13/2024 | B | 200 | |
1/14/2024 | B | 50 | |
1/14/2024 | B | 50 | |
1/15/2024 | A | 50 | |
1/15/2024 | A | 50 |
I need to add the value in a new column also with the value from the past dates matching the item code as below
Date | Item Code | In Qty | Out Qty | NET |
1/12/2024 | A | 100 | 100 | |
1/14/2024 | A | 50 | 50 | |
1/13/2024 | B | 200 | 200 | |
1/14/2024 | B | 50 | 150 | |
1/14/2024 | B | 50 | 100 | |
1/15/2024 | A | 50 | 100 | |
1/15/2024 | A | 50 | 50 |
Please advise
Solved! Go to Solution.
let
fx = (x) =>
[sorted_list = List.Buffer(Table.ToList(Table.Sort(x, "idx"), (x) => x)),
rt = List.Generate(
() => [i = 0, r = sorted_list{0}, net = List.Sum({r{2}, - r{3}})],
(x) => x[i] < List.Count(sorted_list),
(x) => [i = x[i] + 1, r = sorted_list{i}, net = List.Sum({x[net], r{2}, - r{3}})],
(x) => x[r] & {x[net]}
)
][rt],
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
idx = Table.AddIndexColumn(Source, "idx"),
blank_to_nulls = Table.TransformColumns(idx, {}, (x) => if x = " " then null else x),
group = Table.Group(blank_to_nulls, "Item Code", {"x", fx}),
result = Table.RemoveColumns(
Table.Sort(
Table.FromList(List.Combine(group[x]), (x) => x, Table.ColumnNames(Source) & {"idx", "NET"}),
"idx"
),
"idx"
)
in
result
@Omid_Motamedise @lbendlin @AlienSx @Anonymous Thanks for all your support. I used @AlienSx Solution and got the desired result good and fast.
Hi @PravenBabu
Hope these help:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUdJRcgRiQwMDIKkUqwOWMkGWAiJTA5iMMUzGCYiNsGtyQteEW8YU2SJTZNNMsTghFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Code" = _t, #"In Qty" = _t, #"Out Qty" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Item Code", type text}, {"In Qty", Int64.Type}, {"Out Qty", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Item Code", Order.Ascending}, {"Date", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"In Qty", "Out Qty"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Item Code"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}),
Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", (x)=> Table.AddColumn(x , "NET", each List.Sum(List.FirstN(x[In Qty],[Index]))-List.Sum(List.FirstN(x[Out Qty],[Index]) ))}),
#"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"Date", "In Qty", "Out Qty", "NET"}, {"Date", "In Qty", "Out Qty", "NET"})
in
#"Expanded Data"
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Just copy the below code and past it into the advance editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUdJRcgRiQwMDIKmgFKsDljNBllMAYlMDmJQxTMoJiI1waHPC0IZHyhTZMlMUA02xuSMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Code" = _t, #"In Qty" = _t, #"Out Qty" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"In Qty", Int64.Type}, {"Out Qty", Int64.Type}}),
Index = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(Index, "Custom", each [a=Table.SelectRows(Table.FirstN(Index,[Index]),(x)=> x[Item Code]=[Item Code]),b=List.Sum(a[In Qty])-(List.Sum(a[Out Qty])??0)][b])
in
#"Added Custom"
let
fx = (x) =>
[sorted_list = List.Buffer(Table.ToList(Table.Sort(x, "idx"), (x) => x)),
rt = List.Generate(
() => [i = 0, r = sorted_list{0}, net = List.Sum({r{2}, - r{3}})],
(x) => x[i] < List.Count(sorted_list),
(x) => [i = x[i] + 1, r = sorted_list{i}, net = List.Sum({x[net], r{2}, - r{3}})],
(x) => x[r] & {x[net]}
)
][rt],
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
idx = Table.AddIndexColumn(Source, "idx"),
blank_to_nulls = Table.TransformColumns(idx, {}, (x) => if x = " " then null else x),
group = Table.Group(blank_to_nulls, "Item Code", {"x", fx}),
result = Table.RemoveColumns(
Table.Sort(
Table.FromList(List.Combine(group[x]), (x) => x, Table.ColumnNames(Source) & {"idx", "NET"}),
"idx"
),
"idx"
)
in
result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNI3MjAyUdJRcgRiQwMDIKmgFKsDljNBllMAYlMDmJQxTMoJiI1waHPC0IZHyhTZMlMUA02xuSMWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Item Code" = _t, #"In Qty" = _t, #"Out Qty" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"In Qty", Int64.Type}, {"Out Qty", Int64.Type}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Net", (k)=> let t = Table.SelectRows(#"Added Index", each [Item Code]=k[Item Code] and [Index]<=k[Index] )
in (List.Sum(t[In Qty])??0)-(List.Sum(t[Out Qty])??0)
,Int64.Type)
in
#"Added Custom"