XML import columns not updating

[Debbie]

I'm importing data from XML files created elsewhere. No issues with that and it seems to be working fine. But when I update my XML to include an extra column, I can't get PBI desktop to see it. Searching here and I can find several posts about CSV imports saying to increase the column number to force PBI to see the new columns. But my XML import has no column entry. My code just says:

let
    Source = Xml.Tables(File.Contents("L:\Query\xxx.xml")),
    Table = Source{0}[Table]
in
    Table

 I can re-import and get the new column. But then I'll have to rebuild all the associated visuals.

It can't be this hard to do, can it?

Debbie

[v-juanli-msft]

Hi @Debbie 

A s tested, i can update my new added column in source xml file to Power BI Desktop.

I use the xml file here,

Then add a new column "amount" for each category

After clicking on "refresh all", it add a new column "amount" in my table.

Please follow steps here to expand your table after importing data from XML file.

Here is my code in Advanced editor

let
    Source = Xml.Tables(File.Contents("C:\Users\maggiel\Desktop\case\10\10.21\test1.xml")),
    Table0 = Source{0}[Table],
    #"Changed Type" = Table.TransformColumnTypes(Table0,{{"year", Int64.Type}, {"price", type number}, {"Attribute:category", type text}, {"Attribute:cover", type text}}),
    #"Expanded author" = Table.ExpandTableColumn(#"Changed Type", "author", {"Element:Text"}, {"author.Element:Text"}),
    #"Expanded title" = Table.ExpandTableColumn(#"Expanded author", "title", {"Element:Text"}, {"title.Element:Text"})
in
    #"Expanded title"

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Debbie]

Hi Maggie.

Thanks for your reply. I just recreated your example - and it worked all by itself! Created the XML, created a PBIX. Added an extar column to the XML - and as soon as I hit refresh on the PBIX, the new column appeared in the available fields on the left!

So I have no idea why it isn't working for my XML files. The structure is the same. I do have a *lot* more rows in some cases, but I can't see why it's not working. I'm building my XML from a VBS script which is firing SQL against a database. It isn't ideal, but it's a quick-win replacement for a legacy system until we can find time to build a back-end datamart. I just need to get 5 bits of VBS code refreshing on a daily basis and it will do for now.... if I can get it to work!

Debbie