Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |