Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I have a Power BI dashboard that gets data from an RSS feed of a service provider (Request Tracker).
Normally the connection works fine but recently, due to some customers mistakenly adding hexadecimal value to their email, refreshing would result in error as follows:
I've tried to specify encoding with multiple Unicode types as shown in this thread but that would result in another error.
The RSS feed will always inculde the content of the first email from our customer (this is by default from the provider). This means that the invalid characters will keep coming in the future.
The best permanent solutions I can think of is somehow either:
(1) Help Power Query to understand or ignore these characters.
(2) Strip all of the invalid characters from the xml file while parsing in Power Query. If going this way, there is another problem as the RSS link does not point to an xml file but to a search request to collect tickets of the last 12 days, this request will then generate an xml file. I am aware that I can download the file, fix it, then publish it to Power BI but doing that means dashboard refresh will no longer be functioning automatically. We have the dashboard shown on a big TV screen and refresh is scheduled hourly to count the number of request tickets.
Please point me to the right direction. Any help would be greatly appreciated.
Solved! Go to Solution.
I'm posting a solution I found here in case anyone runs into the same problem. The beauty of this is that you can do it without much programming knowledge like I am.
What worked for me is to use Power Automate to get xml file, look into its content, strip the invalid characters before saving the file to SharePoint. After that, I only need to establish data connection between Power BI and the sanitised xml file on SharePoint.
The solution can be found here. It belongs to AlanPs1 and all credits should be given to him for a very neat solution.
I'm posting a solution I found here in case anyone runs into the same problem. The beauty of this is that you can do it without much programming knowledge like I am.
What worked for me is to use Power Automate to get xml file, look into its content, strip the invalid characters before saving the file to SharePoint. After that, I only need to establish data connection between Power BI and the sanitised xml file on SharePoint.
The solution can be found here. It belongs to AlanPs1 and all credits should be given to him for a very neat solution.
(1) Familiarise yourself with the " try ... otherwise ... " concept in Power Query.
Thank you for your input
From what I know, Power Query refuses to parse the xml file in the first place. If data could be read, even with error records, I would have simply deleted the entire "email content" column containing the invalid characters because only request counts is needed and other columns can be used. Unfortunately no matter what I tried, I'm always stuck at the very first step.
As I mentioned in the original post, by default the RSS link will always contain the content of the first email sent to us.
Since I'm not a developer and I'm very new to Power BI, I may have misunderstood your point. Could you please be more specific?
Post a sanitized version of your Power Query code and I will show you how to include the error handling.
let
Source = Xml.Tables(Web.Contents("https://................../?Order=ASC%7CASC%7CASC%7CASC&OrderBy=id%7C%7C%7C&Query=Queue+%3D+%27Property%27+AND+Created+%3E+%2712+DAYS+AGO%27")),
......
in
#"Filtered Rows"
I don't know programming so what you see above is entirely generated by Power Query. There are other lines but I didn't include them as they are just commands to expand tables, remove, filter, etc.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.