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] = null and [Excluded] = null then "All Included" else if [Included] = null and [Excluded] <> null then "All Except "& [Excluded] else "Check Logic"
-(Instead of "All Included", create a row for each distinct value from different table)
-(Instead of "All Except", create a row for each distinct value from different table, except the value listed)
I need help with replacing the "All Included" and "All Except" portions to be replaced with a new row for each value from another table, public ph_sys_domain. Any help is appreciated
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
5 Replies
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
Hi Anonymous, provide some sampe data (as table please) and also expected result.
Cannot attach a spreadsheet for whatever reason... Example below. So the logic would live in Table 1, instead of displaying "All Included", I need to create a new row for each domain_id from Table 2. Same thing for All Except... but to create a new row for each domain_id EXCEPT the one listed. Any suggestions would be appreciated
Table 1:
id Policy Included Final 49897854 All Included 49897850 All Included 1116783753 All Except 2138 1116783753 All Except 2123 810370701 2509 825920450 2509 810370701 2508 810370701 2507 Table 2:
id domain_id 2480421702 2510 2480421701 2509 2478640851 2508 2433555263 2507 2433555262 2506 2433555261 2505 2433555259 2504 2433555257 2503 2433555255 2502 2433555254 2501 2433555251 2499 2338003101 2498 2318861651 2497 2284327654 2495 Hi Anonymous ,
Please check to see if this is what you want.
Here is my sample data:
Put all of these codes into the Advanced Editor:let Source = 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]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"Policy Included Final", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Policy Included Final] = "All Included" then Table2[domain_id] else if Text.StartsWith([Policy Included Final], "All Except") then let exceptDomain = Number.FromText(Text.AfterDelimiter([Policy Included Final], "All Except ")), filteredTable = Table.SelectRows(Table2, each [domain_id] <> exceptDomain) in filteredTable[domain_id] else null), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"), #"Added Conditional Column" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom] = null then [Policy Included Final] else [Custom]), #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Custom.1", Int64.Type}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"}) in #"Removed Columns"And the final output is below:
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.