Keep trailing spaces in Power Query

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

Code:
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"
 
Upvote 0
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!)
 
Upvote 0
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
 
Upvote 0
Yes, you said it right at the top. Serves me right for looking at your answer before my morning coffee. :confused:
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.
 
Upvote 0
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:

Code:
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"
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top