cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Anonymous
Not applicable

M code works in Power BI Desktop but blank when refreshing in Server (on-prem)

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

0 REPLIES 0

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors