Forum Discussion
HELP Power Query Code - Formula for finding a text in a grouped column without expanding
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
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
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 _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.