Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello,
Trying to do somthing that I am sure is simple but cannot figure it out... I havea table with an ID and a Parent ID column. I need to add a custom column that will tell me if, for each ID (row), there is another row where the Parent ID matches that ID.
In Excel, I would use something like "=MATCH(B2,A:A,0)", but cannot figure out how to do in in the Power Bi Query Editor.
I know how to do it with a Merge / Expand, but think there should be something simpler...
Thanks!
I don't know if I would say it is easier, but it can be done without a merge. I'm returning a 1 or 0, but you could wrap that in an if/then/else to return Yes/No or whatever.
The code to do it is below.
The key is this statement:
#"Added Matches" = Table.AddColumn(#"Changed Type", "Matches", each let varID = [ID]
in
Table.RowCount(
Table.SelectRows(#"Changed Type", each [Parent ID] = varID)
)
)
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HcixDQAgCATAXb6mEQTjLIT91/D97nLdWDAkxhpOHSmoK23KXUwyQqy/mHk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Parent ID" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Parent ID", Int64.Type}}),
#"Added Matches" = Table.AddColumn(#"Changed Type", "Matches", each let varID = [ID]
in
Table.RowCount(
Table.SelectRows(#"Changed Type", each [Parent ID] = varID)
)
)
in
#"Added Matches"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMaybe @ImkeF or @edhans can help with a Power Query function to do it. In DAX you would likely use LOOKUPVALUE or something like MAXX(FILTER(...)...)
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
93 | |
90 | |
35 | |
35 |
User | Count |
---|---|
154 | |
102 | |
82 | |
64 | |
54 |