Forum Discussion
XML data refreshing differently in desktop versus service
I am working with an XML file that is saved in sharepoint. I pulled the 2 XML files I need into desktop, everything worked great until I needed to refresh with new files, suddenly all blank (null) values in my table were showing as expandable [Table] values, but they were also all blank tables if I tried expand those tables. So with a little GPT help I got the following to replace empty table values with NULL value and added this as a step in power query, and here is that code:
= let
Source = #"Removed Columns",
ReplaceEmptyTablesWithNull = Table.TransformColumns(
Source,
List.Transform(
Table.ColumnNames(Source),
each {_, (columnValue) => if Type.Is(Value.Type(columnValue), type table) and Table.IsEmpty(columnValue) then null else columnValue}
)
)
in
ReplaceEmptyTablesWithNull
This seems to work perfectly in desktop, anytime I refresh I get all null values instead of [Table].
However when I publish to power BI service and refresh the report all the blank [Table] values come back.
both desktop and service are pointing to the exact same files, I can refresh both at the same time and desktop will handle correctly but service won't. I tried deleting the report and semantic model from service and republishing, no luck. Tried downloading the report from the service and then refreshing that copy in the desktop, that worked in desktop even though the same file was not working in service.
FWIW I am pretty unexperienced with XML and probably dug a hole deeper than I can see out of with GPT, but hey users want what they want and I'm out here trying to make it work 😕
Hi, JayhawkDylan
I am glad to help you.
Since you didn't give me a data source for testing, I assumed an xml file for testing. The first record in my dataset has an empty name.
<root> <record> <name></name> <age>11</age> <email>[email protected]</email> </record> <record> <name>Jane Smith</name> <age>25</age> <email>[email protected]</email> </record> <record> <name>Emily Johnson</name> <age>40</age> <email>[email protected]</email> </record> <record> <name>Michael Brown</name> <age>20</age> <email>[email protected]</email> </record> </root>After connecting the XML file in Power BI Desktop the data looks like this:
Open Power Query Editor, then open 'Advanced Editor', after modifying the M code to the following code：
let Source = Xml.Tables(File.Contents("C:\xxx\xx\xxx\testXML3.xml")), Table0 = Source{0}[Table], #"Changed Type" = Table.TransformColumnTypes(Table0,{{"age", Int64.Type}, {"email", type text}}), Custom1 = Table.TransformColumns(#"Changed Type", {"name", each try (if Table.IsEmpty(_) then null else _[#"Element:Text"]{0}) otherwise _}), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"name", type text}}) in #"Changed Type1"Then change the corresponding column to the corresponding data type, for example, I will change the name to 'Text' type:
Then click Close&Apply.
At this time, the blank data will be displayed as empty:
Modify the data source after publishing the report to Power BI Service: blank-->John Doe
Refresh in Power BI Desktop:
Refresh in Power BI Service:
Then modify the name data 'John Doe' to blank:
Refresh in Power BI Desktop:
Refresh in Power BI Service:
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi, JayhawkDylan
I am glad to help you.
Since you didn't give me a data source for testing, I assumed an xml file for testing. The first record in my dataset has an empty name.
<root> <record> <name></name> <age>11</age> <email>[email protected]</email> </record> <record> <name>Jane Smith</name> <age>25</age> <email>[email protected]</email> </record> <record> <name>Emily Johnson</name> <age>40</age> <email>[email protected]</email> </record> <record> <name>Michael Brown</name> <age>20</age> <email>[email protected]</email> </record> </root>After connecting the XML file in Power BI Desktop the data looks like this:
Open Power Query Editor, then open 'Advanced Editor', after modifying the M code to the following code：
let Source = Xml.Tables(File.Contents("C:\xxx\xx\xxx\testXML3.xml")), Table0 = Source{0}[Table], #"Changed Type" = Table.TransformColumnTypes(Table0,{{"age", Int64.Type}, {"email", type text}}), Custom1 = Table.TransformColumns(#"Changed Type", {"name", each try (if Table.IsEmpty(_) then null else _[#"Element:Text"]{0}) otherwise _}), #"Changed Type1" = Table.TransformColumnTypes(Custom1,{{"name", type text}}) in #"Changed Type1"Then change the corresponding column to the corresponding data type, for example, I will change the name to 'Text' type:
Then click Close&Apply.
At this time, the blank data will be displayed as empty:
Modify the data source after publishing the report to Power BI Service: blank-->John Doe
Refresh in Power BI Desktop:
Refresh in Power BI Service:
Then modify the name data 'John Doe' to blank:
Refresh in Power BI Desktop:
Refresh in Power BI Service:
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.