Good morning! I am posting this on behalf of sqlryan. If you have any suggestions or information, it would be greatly appreciated!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have a cleanup function to remove HTML tags from a text field - it works on Desktop, but once I publish to Report Server and schedule a refresh, the function returns blank instead of the correct value. The refresh doesn't fail and there's no error message - it just returns a blank string. Troubleshooting was tough because you can't view the dataset on Server and we just had a blank field, but I reproduced it in the attached file (contains field w/HTML, and then an example of the broken function - "bad" - and one that works - "good"). It's not a browser issue - Chrome, Edge, and IE all show the same thing - and it displays accurately on the Report Server until we do a scheduled data refresh - then the issue shows itself.
Any idea what's going on here? I'm using the May 2019 Desktop release (tied to our production servers) and I see the same behavior in both the May 2019 and September 2019 Report Server releases. We can use a different function as a work-around, but wanted to see why this one specifically isn't working.
Here's a downloadable example PBIX (with the Excel data file containing basic data so a server refresh can be set up) that shows both functions in action:
http://sqlryan.com/Downloads/HtmlRemovalTest.zip
These are the two functions - they both remove HTML tags in desktop, but this one returns blank on the server:
let
Source = (TextContainingHTML as any) => let
Source = TextContainingHTML,
Result = if TextContainingHTML=null then null else Html.Table(Source, {{"text",":root"}})[text]{0}
in
Result
in
Source
Where this one has the same (working) result in the desktop, but it also works on the server:
let func = (HTML) =>
let
Check = if Value.Is(Value.FromText(HTML), type text) then HTML else "",
Source = Text.From(Check),
SplitAny = Text.SplitAny(Source,"<>"),
ListAlternate = List.Alternate(SplitAny,1,1,1),
ListSelect = List.Select(ListAlternate, each _<>""),
TextCombine = Text.Combine(ListSelect, "")
in
TextCombine
in
func
