Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have the below data with trade, stock and price column where the output price column should be
If Trade= "BUY" then Price corresponding to BUY
Else if Trade= "SELL" then Price corresponding to BUY in all sell rows
So basically the output for all the trades should be the Price for BUY trade i.e. 2000 in our example
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgqNVNJRcnRyVnD2DwoAMo0MDAyUYnWilYJdfXzQpExxS5nhljIHScUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trade = _t, Stock = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Trade", type text}, {"Stock", type text}, {"Price", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Output Price", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Stock]=k[Stock] and [Trade]="BUY")[Price]))
in
#"Added Custom"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Hi @Harshc374 ,
@lbendlin Good Answer!
And I have considered the more complex case of data, you can try this method as well.
Put all of the M function into Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgqNVNJRcnRyVnDOLyoAMo0MDAyUYnWilYJdfXzQpExxS5nhljKHSvmFYkhZ4JayhEpBHOji6gaTMURzILKUKW4pM1SrkKXADowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trade = _t, Stock = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Trade", type text}, {"Stock", type text}, {"Price", Int64.Type}}),
BuyPrice = Table.SelectRows(#"Changed Type", each [Trade] = "BUY"),
BuyPriceOnly = Table.SelectColumns(BuyPrice, {"Stock", "Price"}),
UniqueBuyPrice = Table.Distinct(BuyPriceOnly),
Custom = Table.AddColumn(#"Changed Type", "CustomPrice", each try
if [Trade] = "BUY" or [Trade] = "SELL" then
let
currentStock = [Stock],
priceRecord =
Table.SelectRows(UniqueBuyPrice, each [Stock] = currentStock)
in
if Table.IsEmpty(priceRecord) then null else priceRecord{0}[Price]
else null),
#"Expanded CustomPrice" = Table.ExpandRecordColumn(Custom, "CustomPrice", {"Value"}, {"CustomPrice.Value"})
in
#"Expanded CustomPrice"
The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Harshc374 ,
@lbendlin Good Answer!
And I have considered the more complex case of data, you can try this method as well.
Put all of the M function into Advanced Editor:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgqNVNJRcnRyVnDOLyoAMo0MDAyUYnWilYJdfXzQpExxS5nhljKHSvmFYkhZ4JayhEpBHOji6gaTMURzILKUKW4pM1SrkKXADowFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trade = _t, Stock = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Trade", type text}, {"Stock", type text}, {"Price", Int64.Type}}),
BuyPrice = Table.SelectRows(#"Changed Type", each [Trade] = "BUY"),
BuyPriceOnly = Table.SelectColumns(BuyPrice, {"Stock", "Price"}),
UniqueBuyPrice = Table.Distinct(BuyPriceOnly),
Custom = Table.AddColumn(#"Changed Type", "CustomPrice", each try
if [Trade] = "BUY" or [Trade] = "SELL" then
let
currentStock = [Stock],
priceRecord =
Table.SelectRows(UniqueBuyPrice, each [Stock] = currentStock)
in
if Table.IsEmpty(priceRecord) then null else priceRecord{0}[Price]
else null),
#"Expanded CustomPrice" = Table.ExpandRecordColumn(Custom, "CustomPrice", {"Value"}, {"CustomPrice.Value"})
in
#"Expanded CustomPrice"
The final output is as below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgqNVNJRcnRyVnD2DwoAMo0MDAyUYnWilYJdfXzQpExxS5nhljIHScUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Trade = _t, Stock = _t, Price = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Trade", type text}, {"Stock", type text}, {"Price", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Output Price", (k)=> List.Sum(Table.SelectRows(#"Changed Type",each [Stock]=k[Stock] and [Trade]="BUY")[Price]))
in
#"Added Custom"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |