Displaying Only Matching Rows But Needing Additional Columns Included

[Scott_S_is_me]

Hello all,

I'm needing a way to display only matching values in columns with additional columns needed for context.  For example:

Date	Name	Expense Item	Line Amount
Jan 1 2021	Bob Smith	Car Rental	$100
Jan 1 2021	Bob Smith	Mileage	$30
Jan 2 2021	Bob Smith	Car Rental	$100
Jan 3 2021	Bob Smith	Car Rental	$100

I would like to see only rows that a person expensed car rental and mileage on the same day.  So in the example I would expect to see:

Date	Name	Expense Item	Line Amount
Jan 1 2021	Bob Smith	Car Rental	$100
Jan 1 2021	Bob Smith	Mileage	$30

Any assistance would be greatly appreciated.

[adudani · Accepted solution]

hi @Scott_S_is_me ,

Create a blank query. Copy and paste the following code below into it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMUzBUMDIwMlTSUXLKT1IIzs0syQCynROLFIJS80oSc4AcFUMDA6VYHTzKfTNzUhPTU0FqjRFKjUgz2ZhI5bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Name = _t, #"Expense Item" = _t, #"Line Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}, {"Expense Item", type text}, {"Line Amount", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Name"}, {{"Details", each _, type table [Date=nullable date, Name=nullable text, Expense Item=nullable text, Line Amount=nullable number]}}),
    CheckforValue = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Contains([Details][Expense Item], "Car Rental") and List.Contains([Details][Expense Item], "Mileage") then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(CheckforValue, each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Date", "Name"}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Removed Columns", "Details", {"Date", "Name", "Expense Item", "Line Amount"}, {"Date", "Name", "Expense Item", "Line Amount"})
in
    #"Expanded Details"

Steps taken:

1. Grouped table by Name and Date, added a "Details" column summarizing all distinct rows.

2. Created a "Custom" Column, checking if the "Expense Item" in Detail for the grouped name and date contains the two values, if it does then 1 else 0. ( THIS IS CASE SENSITIVE).

3. Filter the Custom column for values with 1.

4. Removed all columns other than Details. Expanded the Details Column.

File: Expensecondition.pbix

[Scott_S_is_me]

Got it to work.  Thank you so much!