Forum Discussion
List Values based on criteria
Hello, I have a table, public ph_notification_policy_fact, in which I have created a custom column based on fields within that table: =if [Included] <> null then [Included] else if [Included] = ...
Anonymous
If you want to have domain_ids in single row:
let Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY05CoAwEAC/Iltb7JG4m9LCwjdIKrULYqHg8xUVRQjYzgxM14ELFtS8gxLqlIp26tM6jAPE8nGYc0RUqYl6uW2z9eO8FExiPwHLGRihKCrS4dljuCD7wOjO5wu/peWgQow7", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"Policy Included Final" = _t]), Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc+7DcBACAPQXa5OAZjfzRJl/zUSHRShfUI2vu8lmqTCQbKuJca0nuunfJR2a6QrpbVmK2Bm4iiNqZVLPrUTbKjtUp0apZhqpTJVS3nqadPdK4AkAlNrrwBnOnvdMlolFRJeuSzfD88L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, domain_id = _t]), Ad_domainID = Table.AddColumn(Table1, "domain_id", each Text.Combine( if Text.Contains([Policy Included Final], "Except", Comparer.OrdinalIgnoreCase) then List.Select(Table2[domain_id], (a)=> not List.Contains(Text.Split([Policy Included Final], " "), a)) else if Text.Contains([Policy Included Final], "All included", Comparer.OrdinalIgnoreCase) then Table2[domain_id] else List.Select(Table2[domain_id], (b)=> b = [Policy Included Final]), ", "), type text) in Ad_domainID
Anonymous
If you want to have domain_ids in single row:
let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hY05CoAwEAC/Iltb7JG4m9LCwjdIKrULYqHg8xUVRQjYzgxM14ELFtS8gxLqlIp26tM6jAPE8nGYc0RUqYl6uW2z9eO8FExiPwHLGRihKCrS4dljuCD7wOjO5wu/peWgQow7", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, #"Policy Included Final" = _t]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc+7DcBACAPQXa5OAZjfzRJl/zUSHRShfUI2vu8lmqTCQbKuJca0nuunfJR2a6QrpbVmK2Bm4iiNqZVLPrUTbKjtUp0apZhqpTJVS3nqadPdK4AkAlNrrwBnOnvdMlolFRJeuSzfD88L", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, domain_id = _t]),
Ad_domainID = Table.AddColumn(Table1, "domain_id", each Text.Combine(
if Text.Contains([Policy Included Final], "Except", Comparer.OrdinalIgnoreCase) then List.Select(Table2[domain_id], (a)=> not List.Contains(Text.Split([Policy Included Final], " "), a)) else
if Text.Contains([Policy Included Final], "All included", Comparer.OrdinalIgnoreCase) then Table2[domain_id] else
List.Select(Table2[domain_id], (b)=> b = [Policy Included Final]),
", "), type text)
in
Ad_domainIDdufoq3 this worked for me! Thank you very much