The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to Solution.
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
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
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 @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.