Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Filter on Contains but show all

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
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    Why wouldn't my solution not be dynamic?

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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.

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Why wouldn't my solution not be dynamic?

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    9 years ago

    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.