Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
BDale93
Helper I
Helper I

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

 

BDale93_0-1706892304818.png

 

 

1 ACCEPTED SOLUTION
dufoq3
Super User
Super User

@BDale93 

If you want to have domain_ids in single row:

dufoq3_0-1707210869844.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

View solution in original post

5 REPLIES 5
dufoq3
Super User
Super User

@BDale93 

If you want to have domain_ids in single row:

dufoq3_0-1707210869844.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

@dufoq3 this worked for me! Thank you very much 

dufoq3
Super User
Super User

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


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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:

idPolicy Included Final
49897854All Included
49897850All Included
1116783753All Except 2138
1116783753All Except 2123
8103707012509
8259204502509
8103707012508
8103707012507

 

Table 2:

iddomain_id
24804217022510
24804217012509
24786408512508
24335552632507
24335552622506
24335552612505
24335552592504
24335552572503
24335552552502
24335552542501
24335552512499
23380031012498
23188616512497
22843276542495
Anonymous
Not applicable

Hi @BDale93 ,

Please check to see if this is what you want.
Here is my sample data:

vjunyantmsft_0-1707115173704.png

vjunyantmsft_1-1707115185445.png
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:

vjunyantmsft_2-1707115224898.png


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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors