List Values based on criteria

[BDale93]

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

[dufoq3 · Accepted solution]

@BDale93 

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

[BDale93]

@dufoq3 this worked for me! Thank you very much 

[dufoq3]

Hi @BDale93, provide some sampe data (as table please) and also expected result.

[BDale93]

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

[Anonymous]

Hi @BDale93 ,

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.