Filter on Contains but show all

[Anonymous]

I want to be able to filter on a project to see all the resources assign, but i want to see all the projects they are assigned to

[Ashish_Mathur]

Hi @Anonymous,

I answered a similar question here - Filter a column of a Pivot Table on a certain condition but also show other items from that column.

[Anonymous]

Thanks for the responses @Ashish_Mathur are you able to share what is in the AllResources field.  I don't want to connect to an outside source in the file.

@MarcelBeug I need this to be dynamic so i can switch to project 2 if needed.

[Ashish_Mathur]

Hi @Anonymous,

All Resources is a column which i have generated in Power Query using this M code

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"Client Name", type text}, {"Resource", type text}, {"Project ID", Int64.Type}, {"Billable amount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Client ID"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([Count][Resource], ", ")),
    #"Expanded Count" = Table.ExpandTableColumn(#"Added Custom", "Count", {"Client Name", "Resource", "Project ID", "Billable amount"}, {"Client Name", "Resource", "Project ID", "Billable amount"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"Billable amount", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "All resources"}})
in
    #"Renamed Columns"

Hope this helps.

[MarcelBeug · Accepted solution]

Why wouldn't my solution not be dynamic?

[MarcelBeug]

It can be done with the following query.

Notice that the major part of the query takes care of creating blanks in the "Resource" column for repeating resources, so you might want to reconsider this part.

let
    Source = Table.NestedJoin(Table1,{"Project"},Filter,{"Filter"},"Filter",JoinKind.Inner),
    #"Removed Columns" = Table.RemoveColumns(Source,{"Filter"}),
    #"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"Resource"},Table1,{"Resource"},"Table1",JoinKind.LeftOuter),
    #"Removed Columns1" = Table.RemoveColumns(#"Merged Queries",{"Project", "Resource"}),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Removed Columns1", "Table1", {"Project", "Resource"}, {"Project", "Resource"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Table1",{{"Resource", Order.Ascending}, {"Project", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries1" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries1", "Previous", {"Resource"}, {"Previous.Resource"}),
    #"Sorted Rows1" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each if [Resource] = [Previous.Resource] then null else [Resource]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Resource", "Index", "Index.1", "Previous.Resource"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "Resource"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Resource", "Project"})
in
    #"Reordered Columns"