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 yr2...
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.