Forum Discussion
Rename XML elements before actual import
All,
I have the following challenge when importing XML files into Power BI. The XML file contains a structure with numerous fields, but the last 50 elements are always 50 years with element name yr20xx. The start value however differs per year, so for instance in 2022 the first element will be yr2023, but next year the first element will be yr2024.
All files will have the same structure, so next year all files will start with yr2024 and will contain the next 49 years.
A simplified structure of the xml in 2022 would be:
<object>
<key>1</key>
<yr2023>12</yr2023>
<yr2024>13</yr2024>
<yr2025>14</yr2025>
<yr2026>15</yr2026>
<yr2027>16</yr2027>
</object>
A simplified structure of the xml in 2023 would be:
<object>
<key>1</key>
<yr2024>13</yr2024>
<yr2025>14</yr2025>
<yr2026>15</yr2026>
<yr2027>16</yr2027>
<yr2028>17</yr2028>
</object>
In the end I want to end with the below table structure:
| Key | Year | Value |
| 1 | 2023 | 12 |
| 1 | 2024 | 13 |
| 1 | 2025 | 14 |
| etc. |
My first thought on solving this, was:
- create a parameter containing the start year, e.q. 2023 or 2024 or else.
- In the import of the XML the first step should be the replace the name of the yr20xx elements with a sequence from 1 to 50
- Next unpivot the result and add the start year to the year value
My main question: Is it possible (and how?) to change the XML element name, so it will always be 1 to 50 and the import will be generic?
To have this working I think the structure must be changed before it is actually processed in power query.
My workaround is to have the above steps executed in a separate Excel, before importing the data, but my preference is of course to have all in the import
3 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc8xDoAgDIXhu3ABkFI4DLJIuuhgYnTg9ia2MS9h6//yLa3VrU8I1M9tl35/t+hyyNBctP0/aI8rhkhGohkcgSVjhCxNjI0lZDyxbIyR5YkVYxlZQebxa9faCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Parsed XML" = Table.TransformColumns(Source,{},Xml.Tables), CN = Table.ColumnNames(#"Parsed XML"[Column1]{0}), #"Expanded Column1" = Table.ExpandTableColumn(#"Parsed XML", "Column1", CN), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded Column1", {"key"}, "Attribute", "Value"), #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","yr","",Replacer.ReplaceText,{"Attribute", "Value"}), #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"Attribute", "Year"}}), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}, {"Year", Int64.Type}, {"key", Int64.Type}}) in #"Changed Type"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Hi lbendlin,
Thanks for the reply. It looks like this is a solution.
I'm now trying to implement your solution for a folder containing multiple xml files, like the 2 examples I mentioned in the question. Challenge for me is how to trigger the conversion process per file 🙂
What I usually do is the conversion for one of the files. Then I steal the code and make it into a function. Then you can call that function for each of the files.
