Powerquery: Check if value exists in column as part of a string

Question

Using Power Query in Power BI, I need to check for each row of a column to see if the value already exists as part of a larger string or is identical to a string in the column

If it does, then that value should be marked as already existing, if not it should be marked as being unique

ID	string	result
1	"ABC,DEF,GHI,JKL"	unique
2	"DEF,GHI,JKL"	already exists
3	"MNO,PQR"	unique
4	"STU"	unique
5	"GHI,JKL"	already exists
6	"JKL"	already exists
7	"STU"	already exists

I suspect I would need to create a list to make the check, but I cannot figure out how I could create these lists correctly.

The items on first row are not unique. It is just first. Is that the rule - First is always shown as unique? Why does that make sense if we get a different answer if data sorts differently. What if we had ABC,DGG in row 2, is that unique or already exists because part of it was on row 1 and part of it does not show up elsewhere. — horseyride
– horseyride, Commented Jul 15, 2022 at 15:11
@horseyride, thank you very much for the follow-up! ABC,DGG would be unique as it would be a different string. Maybe I should have included quote marks to make myself better understood, but it is the nature of this data that is a bit strange.| The data in the string is a hierarchy. Level 1, Level 2, Level 3, Level 4 (ABC,DEF,GHI,JKL) What I am trying to understand is how I could tell PowerBI to remove cells where it finds just Level 3, Level 4 data (GHI,JKL) for example. — Displaynameunknown
– Displaynameunknown, Commented Jul 15, 2022 at 15:17

davidebacci · Accepted Answer · 2022-07-15 15:31:58Z

1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlRQ0lFydHLWcXF103H38NTx8vZRUIrViVYyAslgiBqDRH39/HUCAoMgIiYgkeCQUAjPFMRD0WEGEoHzzBGqYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, string = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "result", each 
let a = List.Range(#"Changed Type"[string], 0,[ID]-1 ),
b = List.FindText(a, [string]),
c = if List.Count(b) > 0 then "already exists" else "unique"
in c
)
in
    #"Added Custom"

answered Jul 15, 2022 at 15:31

davidebacci

31.4k4 gold badges20 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Displaynameunknown Over a year ago

Thank you very much, David! It seems that the formula is working, I will now need to validate it and see if I have any exceptions lingering around.

Collectives™ on Stack Overflow

Powerquery: Check if value exists in column as part of a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related