Dec 02 2019 01:49 PM
Hi,
I need help. I have been searching for a formula that I can use but I'm not having any luck.
I am creating a new spreadsheet for a new work group I am in that requires a list from data manually entered. I am only interested in ACTIVE rows under the Status Field (Green Column) that relate to the specific employee (Blue Column).
Essentially I want a formula to create a list:
If the Blue Column contains Sam AND the Green Column contains Active THEN create a list of the related Yellow Column, Red Column and Purple Column.
I have attached a very basic image of the spreadsheet.
Any help would be excellent!!
Thanks in advance.
Dec 02 2019 02:49 PM
I personally would pass the [Employee] as a parameter to Power Query and return a filtered table as described in https://exceleratorbi.com.au/pass-excel-parameter-power-query/.
The only downside is that a Refresh is required so depending on your data that may not be a feasible solution.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Status", type text}, {"Supplier", type text}, {"Driver", type text}, {"Notes", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Employee] = Parameter)),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Status] = "Active")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Employee", "Status"})
in
#"Removed Columns"
Dec 02 2019 03:31 PM
Hi @TheDon4500
Is a slicer on a table or pivot table any good?
You can just copy paste into the new sheet then.