Hi guys,
I have a power query list which has a column [Products] containing strings. I want to filter the table with this column by using multiple strings (keywords) that are contained in a list (Table4).
I tried to set this up by creating a new check column, which I would then use to filter the table. However, the column will just read "function". Any help?
let
FilePath = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content]{0}[Column1],
FileName = Excel.CurrentWorkbook(){[Name="FileName"]}[Content]{0}[Column1],
Source = Excel.Workbook(File.Contents(FilePath & FileName), null, true),
Checkpoints_Sheet = Source{[Item="Checkpoints",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Checkpoints_Sheet,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Check", each (C) => List.AnyTrue(List.Transform(Table4, each Text.Contains(C[Products], _))))
in
#"Added Custom"
Hi Anonymous
Right now you are returning a function for each row. Drop the first each in your last step (the one in red below)
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Check", each (C) => List.AnyTrue(List.Transform(Table4, each Text.Contains(C[Products], _))) )
By eliminating that each, the step will return the result of the List.AnyTrue( ... ). Do make sure Table4 is actually a list, since you're passing it as first argument fro the List.Transform( )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi Anonymous
Right now you are returning a function for each row. Drop the first each in your last step (the one in red below)
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Check", each (C) => List.AnyTrue(List.Transform(Table4, each Text.Contains(C[Products], _))) )
By eliminating that each, the step will return the result of the List.AnyTrue( ... ). Do make sure Table4 is actually a list, since you're passing it as first argument fro the List.Transform( )
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
