Hi @PBInewbie17
This code does I believe what your text says, but it does not return your table of expected data. My column is the Include_Exclude2 column.
Example:
Your second to last row for FHA returns Exclude. Mine returns Include. That is because your 2nd bullet point says "if the FHA is a duplicate (meaning the FHA column has the number more than 1 time), and the Decision Type has Firm Finally Rejected and Firm Issued, then everything for that duplicated FHA should be include. So mine returns Include. For every 11230077. You incorrectly show Exclude for 3 records of that FHA#, at least per your decription.
Here is my code, which does a TON of list manipulation to do this.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLasMwEEV/RXgd0OgtL9NHINBFyTZkURwVHBzXOAk0f99RbCxRT9IuhrEkfLj3zmy3hRBKWQBXLIol1qruj2zZdWwTqj2ePVfAJUiJ30I7HxvW63fVXPah2C1ownsfmvqIkANnL+GzurK3c4+PJZdywpkSsMl/4FZ1+9E018gL1TlEYUJykVAOIkphrduE8kYZCxZvnwhrCBATQCogrA0AlwPuOdNcqIymCWcDzec0wpjlSZWS922VI2cTvrrQsoQbYbe08+GZyNKUJgF/aBKjOXUbmzHYzGxsGCG4mNUzuUcmpeNKco3mgAdrlDICeovmNMIYJpQFZImwf4Eepq2z1fZE2JSo9el0GaXY6fdhuiaXsvsB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"FHA Number" = _t, #"Property Name" = _t, #"Decision Type" = _t, #"Decision Date" = _t, dap_status_id = _t, Sequence = _t, Include_Exclude = _t]),
#"Removed Other Columns" = Table.SelectColumns(Source,{"FHA Number", "Decision Type"}),
#"Grouped Rows" =
Table.Group(
#"Removed Other Columns",
{"FHA Number"},
{
{"Decision Type List", each _[Decision Type], type list}}
),
#"Merged Source with Grouped Rows" = Table.NestedJoin(Source, {"FHA Number"}, #"Grouped Rows", {"FHA Number"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Source with Grouped Rows", "Grouped Rows", {"Decision Type List"}, {"Decision Type List"}),
#"Added Include_Exclude" =
Table.AddColumn(
#"Expanded Grouped Rows",
"Include_Exclude2",
each
if List.Count([Decision Type List]) > 1
and List.ContainsAll(
[Decision Type List],
{"Firm Issued", "Firm Finally Rejected", "Reopen Firm Final Reject"}
)
then "Include" else
if List.Count([Decision Type List]) > 1
and List.Count(List.Select([Decision Type List], each _ = "Firm Finally Rejected")) > 1
and List.Contains({"Firm Issued"},[Decision Type List]) = false
then "Exclude" else
if List.Contains(
{"Firm App Recd", "Firm Prelim Rej/ Defcy Ltr", "Reopen Firm Final Reject", "Reopen Firm Prel Rej/ Defcy"},
[Decision Type]
)
then "Exclude" else "Include",
type text
),
#"Removed Columns" = Table.RemoveColumns(#"Added Include_Exclude",{"Decision Type List"})
in
#"Removed Columns"
If that isn't correct, please review your bullet point requirements or make sure your expected result table is accurate.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
Can you explain how this is Exclude? It does not fit into any of your 3 new critera.
- It isn't in your first list.
- It isn't in 2 & 3 either because those involve multiple values, and this is a unique FHA.
That isn't the only one I am questioning, but I cannot get from your description to the returned values, and I cannot do code if I don't understand the base logic.
Please see the image below for a justification.
- If [Decision Type] contains multiple "Firm Finally Rejected" items and does not contain "Firm Issued", then the maximum [Sequence] matching the "Firm Finally Rejected" should equal "Exclude". The other "Firm Finally Rejected" record that is not the maxium should equal "Include".
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
