column with values ​​in rows with related

[william117]

A way to create a new column that contains the values ​​that are with the same movement id and name id, I already used Dax I still can't find a way. please help me.
I leave the example.
table AAA

It is a comparison of purchases when it was sold, but the table is saved in a single movement in the same table, I need to compare them but when I make two tables to compare account_id in and out, it does not show the data correctly. I need to compare them. but everything is in the same table and separating them was not the best idea.

thanks

[Anonymous · Accepted solution]

Hi @william117,

I think you can do these operations in 'query editor' with 'group', 'fill' and 'replace value' functions:

Result:

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjFT0lEyBGEDQ10QXVCUn1KaXAJmmwKxgVKsDrIyQwNdIyRlRmCtqOqMcBgHUmZoiqYOi3kgdcZQ88xh9pqiqMvXNQWrQlhsDjMQq0Kw1WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"move _id" = _t, account_id = _t, #"name id" = _t, name = _t, #"in" = _t, out = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"move _id", Int64.Type}, {"account_id", Int64.Type}, {"name id", type text}, {"name", type text}, {"in", Int64.Type}, {"out", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"move _id", "name id", "name"}, {{"Contents", each Table.ReplaceValue(Table.FillUp(Table.AddColumn(_, "Custom", each if [out] <> 0 then [out] else null),{"Custom"}),each [Custom],each if [Custom]= [out] then 0 else [Custom],Replacer.ReplaceValue,{"Custom"}), type table}}),
    #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"account_id", "in", "out", "Custom"}, {"account_id", "in", "out", "Custom"})
in
    #"Expanded Contents"

Regards,

Xiaoxin Sheng

[KNP]

Hi @william117,

You may need to take a little more time to explain the scenario and provide some data.

Can I suggest this post from @Greg_Deckler. 

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523#M6071...