What's the logic? I can't help feeling that you have radically over-simplified your sample data, but ...
M Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Notes", type text}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Notes], "jackpot") then "jackpot" else null),
#"Replaced Errors" = Table.ReplaceErrorValues(#"Added Conditional Column", {{"Custom", ""}})
in
#"Replaced Errors"
Or:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Notes", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Notes], "jackpot"))
in
#"Filtered Rows"
However you can do the latter by using a regular text filter on a regular table directly on the Excel page - no need for any form of pivot!
Bookmarks