Forum Discussion
Trim and Clean not working to remove hidden space/row from SharePoint list data set
Actually when you paste the data, then you will lose whitespaces. Hence, from pasted data, it is not possible to know what whitespaces are there. While it will need the sample data to see what whitespaces are there but there is another way to retrieve what you want. Since you have only text and numbers to be retrieved+space, Just insert following statement where data is the column which has Example A, Example B results and Source is your previous step
= Table.ReplaceValue(Source,each [data],each Text.Select([data],{"0".."9","a".."z","A".."Z"," "}),Replacer.ReplaceText,{"data"})
Most Valuable ProfessionalActually when you paste the data, then you will lose whitespaces. Hence, from pasted data, it is not possible to know what whitespaces are there. While it will need the sample data to see what whitespaces are there but there is another way to retrieve what you want. Since you have only text and numbers to be retrieved+space, Just insert following statement where data is the column which has Example A, Example B results and Source is your previous step
= Table.ReplaceValue(Source,each [data],each Text.Select([data],{"0".."9","a".."z","A".."Z"," "}),Replacer.ReplaceText,{"data"})
Thanks Vijay_A_Verma
I wasn't able to apply correctly. Token Comma expected at {"0".."9","a".."z","A".."Z"," "}
The last few lines i have is:
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Inserted Text Before Delimiter", {{"Text Before Delimiter", each Text.AfterDelimiter(_, ">", {0, RelativePosition.FromEnd}), type text}}),
#"Renamed System / Serv impacted" = Table.RenameColumns(#"Extracted Text After Delimiter",{{"Text Before Delimiter", "System / Serv impacted"}}),
#"Replaced Value Test" = Table.ReplaceValue(#"Renamed System / Serv impacted”,each [System / Serv impacted],each Text.Select([System / Serv impacted],{"0".."9","a".."z","A".."Z"," "}),Replacer.ReplaceText,{"System / Serv impacted"})
in
#"Replaced Value Test"
Any further suggestions would be amazing.
- Vijay_A_Verma
Since you have a / in your column name, you need to refer it everywhere as
[#"System / Serv impacted"]
Thankyou so much Vijay. Worked a treat. Brilliant!!!