HELP Power Query Code - Formula for finding a text in a grouped column without expanding

[martinblom]

Hello everyone,

I need your help to solve a problem.

This is my data:

Step 1:

Grouped Rows by PersonID

Step 2:

Add Custom Column with the code: Table.Transpose(Table.SelectColumns([Antal], {"Betalingskanal"}))

Step 3:

Now what I want is something like this: - if [GOD] = "MOBILEPAY" then true else false, but it does not work.

It says false, but should be true.

I think it is because there are 13 columns, so I have to tell the formula, that I do not care what column it is, it has to take ALL columns, and look for the word inside the columns and see if it match with the word.

I hope you guys understand what I am trying.

OBS! I need to do all this without expanding the columns, it is very important. In my other query i got over 1 million ID's. You are not able to expand them all due to crashed, time etc.

Thank you very much for your help.

Best regards,

Martin

[v-yanjiang-msft · Accepted solution]

Hi @martinblom ,

According to your description, here's my solution.

Add a custom column.

if List.Contains([Antal][Betalingskanal],"MOBILEPAY") then "True" else "False"

I modify the sample to:

And get the correct result:

The whole M syatax is:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZC7CsJAEEX/ZetI5s5rd0oDEYKKgpWEFJaC/n/r2okGSbo5cA5cZhyTUpYCkGtqkmYJeCHOFY6nbjj05+213kzMLahFpKn5ioKMQyHZK3SXRba5vaHfPG/3x6JC2K3Cbtj/6uYKFrcSHzqwTuc/e2YLXV2IzX1oegE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PersonID = _t, AccountID = _t, Betalingskanal = _t, DWCreatedDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PersonID", Int64.Type}, {"AccountID", Int64.Type}, {"Betalingskanal", type text}, {"DWCreatedDate", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"PersonID"}, {{"Antal", each _, type table [PersonID=nullable number, AccountID=nullable number, Betalingskanal=nullable text, DWCreatedDate=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Contains([Antal][Betalingskanal],"MOBILEPAY") then "True" else "False")
in
    #"Added Custom"

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.