Keep trailing spaces in Power Query

[macfuller]

I use a handy process from Rob Collie's site to find item descriptions that contain certain text. However, the problem is that Power Query strips trailing spaces from my search values. So if I search for "rat " with a space I get "laboratory", "calibrator", and other variations because PQ has stripped the trailing space.

let

/*
     List of search terms to match against
*/


    SourceList = {"Angiocath",
                    "Bandage",
                    "Catheter",
                    "Electrode",
                    "Glove",
                    "Latex",
                    "Latex Free",
                    "Mice",
                    "Mouse",
                    "MRI Safe",
                    "Needle",
                    "Rat ",
                    "Screw",
                    "Sponge",
                    "Stent",
                    "Suture",
                    "Syringe",
                    "Wire"},


    StartList = Table.FromList(SourceList,Splitter.SplitByNothing(),{"Keyword"}),
    ChangeToText = Table.TransformColumnTypes(StartList,{{"Keyword", type text}}),
    AddIndex = Table.AddIndexColumn(ChangeToText, "Keyword Index", 1, 1),
    ChangeToInteger = Table.TransformColumnTypes(AddIndex,{{"Keyword Index", Int64.Type}})


in
    ChangeToInteger

How can I get PQ to retain the space so I can only return rodents?

 

[horseyride]

Does this help? Table1 (no header row) contains list of words to find (trailing spaces ok) and Table 2 (no header row) contains the Descriptions to search. Adds True/False column for matches

let
    Find= Table.ToList(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]) as list,
    Desc = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.AddColumn(Desc, "Custom", each List.AnyTrue(List.Transform(Find, (substring) => Text.Contains([Column1], substring))))
in #"Added Custom"

 

[macfuller]

My file is huge so I'm thinking it's quicker to ask you this question than test the code <g>. Does your solution add all instances of the matched terms to the new column? (As long as it keeps the space I'm OK with having the match take place in PQ or in a DAX calculated column as in Rob's solution!)

This ties to a question that I posted a while ago but couldn't get a DAX answer for, maybe it would work in PQ. I would like to be able to exclude certain terms. For example I want to find all items that are latex-free. So if I'm searching for "Latex" I don't want "Latex Free" to appear as a match. (Leaving aside all the ways manufacturers indicate their items are made without latex!)

 

[horseyride]

This adds a True/False for matching terms, but does not flag which term matches, nor does it have an exclude list so that latex-free is not a match for latex

 

[macfuller]

Yes, you said it right at the top. Serves me right for looking at your answer before my morning coffee.
Importing from the spreadsheet table rather than using a PQ list appears to retain the space at least (though I'd like to know how to retain the space using a list). But I have about 30 medical terms I search for and I'd like to be able to use a slicer to pick which items to display based on the term listed. Rob's DAX provides only the first match rather than all, but it's a start.

 

[horseyride]

Sorry cant help

 

[horseyride]

Table1 (no header row) contains list of words to find (trailing spaces ok)
Table2 (no header row) contains the Descriptions to search
Table3 (no header row) contains the list of words that override and disqualify a description match
There's probably a function method, I didn't take the time
M code for PQ:

let
    Find= Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Exclude = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

//generate list of matching terms for each description as new column
    #"Added Custom" = Table.AddColumn(List, "Custom", each {0..Table.RowCount(Find)-1}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom.1", each if Text.Contains([Column1],Find{[Custom]}[Column1]) then Find{[Custom]}[Column1] else null),
    #"Removed Duplicates" = Table.Distinct(#"Added Custom1", {"Column1", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([Custom.1] <> null)),
    Grouped = Table.Group(#"Filtered Rows", {"Column1"}, {{"Matches", each Text.Combine([Custom.1], ", "), type text}}),

//generate list of excluding terms for each description as new column
    #"Added Custom2" = Table.AddColumn(List, "Custom2", each {0..Table.RowCount(Exclude)-1}),
    #"Expanded Custom2" = Table.ExpandListColumn(#"Added Custom2", "Custom2"),
    #"Added Custom3" = Table.AddColumn(#"Expanded Custom2" , "Custom.2", each if Text.Contains([Column1],Exclude{[Custom2]}[Column1]) then Exclude{[Custom2]}[Column1] else null),
    #"Removed Duplicates1" = Table.Distinct(#"Added Custom3", {"Column1", "Custom.2"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Duplicates1", each ([Custom.2] <> null)),
    Grouped2 = Table.Group(#"Filtered Rows2", {"Column1"}, {{"Excluded", each Text.Combine([Custom.2], ", "), type text}}),

//merges the two above, and erases any match where there is an exclusion

    #"Merged Queries" = Table.NestedJoin(List,{"Column1"},Grouped2,{"Column1"},"Grouped2",JoinKind.LeftOuter),
    #"Expanded ExcludeList" = Table.ExpandTableColumn(#"Merged Queries", "Grouped2", {"Excluded"}, {"Excluded"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded ExcludeList",{"Column1"},Grouped,{"Column1"},"Grouped",JoinKind.LeftOuter),
    #"Expanded IncludeList" = Table.ExpandTableColumn(#"Merged Queries1", "Grouped", {"Matches"}, {"Matches.1"}),
    #"Added Custom4" = Table.AddColumn(#"Expanded IncludeList", "Matches", each if [Excluded]=null then [Matches.1] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom4",{"Matches.1"})

in #"Removed Columns"

 

[macfuller]

Thanks! Very imaginative.

 

[macfuller]

I've had a chance to run this against my data. While the trailing space remains in the Power Query environment when I view the final table (imported from the spreadsheet rather than generated in PQ using the original code), when it gets loaded to the data model as a Power Pivot table the trailing space is lost and the match rules pull up "refrigerator" and all the other possibilities.

What is it about loading to DAX that cuts the trailing spaces? Is there a setting or parameter to be changed that will stop it from doing that? This is going to affect other measures besides the search so I'd like to know how to get DAX to import exactly what I want it to.

 

[horseyride]

Sorry, not a DAX'er myself