Search code examples
excelexcel-formulapowerquery

Power Query Filtered Rows based on value

I have two sources - a master list (Source1) and a weekly update (Source2). I want to check against ID numbers in Source2

  1. filter for IDs that are not in Source1 and

  2. check that the new IDs in Source2 doesn't contain the string "Closed" in the "Status"column in Source2

I want the master list to basically include all the new IDs added in the weekly update list and make sure not to include any IDs that have been closed.

let
  // Merge Source1 and Source2 (use a Full Outer Join to get all rows from both tables)
  MergedTables = Table.NestedJoin(Source1, {"ID"}, Source2, {"ID"}, JoinKind.FullOuter),

  // Expand Source2 columns
  ExpandedSource2Data = Table.ExpandTableColumn(#"MergedTables", 
    {{"Title",each Text.From(_)}, {"Problem Description",each Text.From(_)}, {"Originator",each Text.From(_)}, {"WOA/Event/Dev Number",each Text.From(_)}, {"Status", each Text.From(_)}, {"Impacted Subsystems", each Text.From(_)}, {"Date Entered", each Text.From(_)}, {"Date Closed", each Text.From(_)}, {"Problem Date",each Text.From(_)}, {"Disposition to Close", each Text.From(_)}, {"Root Cause of the Problem",each Text.From(_)}, {"Final Disposition", each Text.From(_)}}, 
    {{"Source2_Title",each Text.From(_)}, {"Source2_Problem Description",each Text.From(_)}, {"Source2_Originator",each Text.From(_)}, {"Source2_WOA/Event/Dev Number",each Text.From(_)}, {"Source2_Status",each Text.From(_)}, {"Source2_Impacted Subsystems",each Text.From(_)}, {"Source2_Date Entered",each Text.From(_)}, {"Source2_Date Closed",each Text.From(_)}, {"Source2_Problem Date",each Text.From(_)}, {"Source2_Disposition to Close",each Text.From(_)}, {"Source2_Root Cause of the Problem",each Text.From(_)}, {"Source2_Final Disposition", each Text.From(_)}}), 

  // Filter rows where ID is in Source2 but not in Source1 and Status in Source2 is not 'Closed'
  FilteredRows = Table.SelectRows(#"ExpandedSource2Data", each ([ID] <> null and [Source2][ID] <> null and [Source2][Status] <> "PR Closed")),

  // Combine Source1 and FilteredRows
  CombinedTables = Table.Combine({Source1, FilteredRows}),
  #"Removed columns" = Table.RemoveColumns(CombinedTables, {"Source2_Title", "Source2_Problem Description", "Source2_Originator", "Source2_WOA/Event/Dev Number", "Source2_Status", "Source2_Impacted Subsystems", "Source2_Date Entered", "Source2_Date Closed", "Source2_Problem Date", "Source2_Disposition to Close", "Source2_Root Cause of the Problem", "Source2_Final Disposition"})

in
  #"Removed columns"

I added the each Text.From(_) because it keeps on giving me an error stating : [Expression.Error] We cannot convert the value 3 to type Text....although it is still not resolved.

Example data: Source1/master list:

ID Title Description Conclusion Status
120 Title120 Desc120 Conclusion120 120 Closed
137 Title137 Desc137 Conclusion137 137 Open
142 Title142 Desc142 Conclusion142 142 Fields Complete

Source2/weekly update list:

ID Title Description Conclusion Status
120 Title120 Desc120 Conclusion120 120 Closed
137 Title137 Desc137 Conclusion137 137 Open
142 Title142 Desc142 Conclusion142 142 Fields Complete
001 Title001 Desc001 Conclusion001 001 Closed
006 Title006 Desc006 Conclusion006 006 ID Closed
600 Title600 Desc600 Conclusion600 600 NEW
700 Title700 Desc700 Conclusion700 700 Open

Desired merged output with only the new IDs added to the master list but not anything that was closed a while back and not in master:

ID Title Description Conclusion Status
120 Title120 Desc120 Conclusion120 120 Closed
137 Title137 Desc137 Conclusion137 137 Open
142 Title142 Desc142 Conclusion142 142 Fields Complete
600 Title600 Desc600 Conclusion600 600 NEW
700 Title700 Desc700 Conclusion700 700 Open
Solution 
  • //powerquery code for Table1 that merges in Table2 where ID is new and Table2.Status does not contain Closed
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Merged Queries" = Table.NestedJoin(Source, {"ID"},Table.SelectRows(Table2, each [Status]=null or not Text.Contains([Status],"Closed")), {"ID"}, "Table2", JoinKind.RightAnti),
#"Removed Other Columns" = Table.SelectColumns(#"Merged Queries",{"Table2"}),
ColumnsToExpand  =List.Distinct(List.Combine(List.Transform(Table.Column(#"Removed Other Columns", "Table2"), each if _ is table then Table.ColumnNames(_) else {}))),
#"Expanded Table" = Source & Table.ExpandTableColumn(#"Removed Other Columns", "Table2", ColumnsToExpand, ColumnsToExpand)
in #"Expanded Table"

    enter image description here