I have a File called Data, that is both the Sheet name and the Query Name. I created a Sheet name and the Query Named FilterList. I then transformed it to a list. This list corresponds to the field in Column A of the Data Query, which is named TDL#. So Anything in my FilterList needs to be excluded from my TDL# column in the Data sheet.
let
Source = Excel.Workbook(File.Contents("C:\Users\Project Test\source data\Test.xlsx"), null, true),
in_Sheet = Source{[Item="in",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(in_Sheet, [PromoteAllScalars=true]),
#"Inserted Text Before Delimiter" = Table.AddColumn(#"Promoted Headers", "TDL#", each Text.BeforeDelimiter([Group], " "), type text),
#"Renamed Columns" = Table.RenameColumns(#"Inserted Text Before Delimiter",{{"User Name", "Group Member"}, {"User Email", "Group Member Email"}, {"Group", "Group Name"}}),
#"Added Custom-File Path" = Table.AddColumn(#"Renamed Columns", "File Path", each fnGetParameter("File Path")),
#"Added Custom-Initial Workflow" = Table.AddColumn(#"Added Custom-File Path", "Initial Workflow", each fnGetParameter("Initial Workflow")),
#"Added Custom-Year" = Table.AddColumn(#"Added Custom-Initial Workflow", "Year", each fnGetParameter("Year")),
#"Added Custom-Quarter" = Table.AddColumn(#"Added Custom-Year", "Quarter", each fnGetParameter("Quarter")),
#"Added Custom-Primary" = Table.AddColumn(#"Added Custom-Quarter", "Primary Column", each [Year] & " Period " & [Quarter]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom-Primary",{{"Group Member", type text}, {"Group Member Email", type text}, {"Group Name", type text}, {"File Path", type text}, {"Year", type text}, {"Quarter", type text}, {"RunDate", type date}, {"Initial Workflow", type date}, {"Primary Column", type text}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Initial Workflow", "Group Name", "Group Member Email", "Group Member", "Quarter", "Year", "Primary Column", "TDL#"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"TDL#", "Primary Column", "Quarter", "Year", "Group Member", "Group Member Email", "Group Name", "Initial Workflow"})
in
#"Reordered Columns"
@webbers can you please supply your file (or a mock up with dummy data) so we don't have to recreate everything
Hello,
From your Data query, do a merge with the FilterList and use left anti join.
Br,
Anders
@sehlsan ,
I did not even think of that! And I think that is actually the simpliest solution. Especially when you consider that it will not be ME that maintains this, but other people. They can just got into the FILTER file, add the TDL#s that have closed, save then open the main file and Refresh All.... Easy peasy!!! Thanks!
@philipt ,
Appreciate your prompt response, but I went with the solution that was provided by Anders.
