Need help to convert XML file to PowerBI report

[Raggsokk]

I have problems getting the data from this file into a report. The report is directly from a backup report I get each month.
The file is formated at .xls but is xml. I can open it in excel and convert it, but I would like to have it automatically into powerbi.

When I try to get the data out in power query I only manage to get all the infromation in one single column.

File:

https://docs.google.com/spreadsheets/d/1UgvzzKghXFfYn6v8NWJXCl-3wH1igOUu/edit?usp=sharing&ouid=11130...

This is the data I need from the file, in a PowerBI repport.

[lbendlin · Accepted solution]

Let me be the first one to say that this is seriously messed up.  Not just the file name but the format of the XML is atrocious as well.

let
    Source = Xml.Tables(File.Contents("C:\Users\xxx\Downloads\vmware_Backup_Mengder_All_vcenter.xls")),
    Table = Source{3}[Table],
    Table1 = Table{0}[Table],
    Table2 = Table1{1}[Table],
    #"Removed Other Columns" = Table.SelectColumns(Table2,{"Cell"}),
    #"Expanded Cell" = Table.ExpandTableColumn(#"Removed Other Columns", "Cell", {"Data"}, {"Data"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Expanded Cell", "Data", {"Element:Text"}, {"Element:Text"}),
    #"Removed Top Rows" = Table.Skip(#"Expanded Data",45),
    #"Added Index" = Table.AddIndexColumn(#"Removed Top Rows", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "sequence", each if [Index]<9 then Number.Mod([Index]-1,8) else if [Index]=9 then null else Number.Mod([Index]-2,8)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "group", each if [Index]<10 then 0 else Number.RoundDown(([Index]-2)/8)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([sequence] <> null)),
    #"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Element:Text", "sequence", "group"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Other Columns1", {{"sequence", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Other Columns1", {{"sequence", type text}}, "en-US")[sequence]), "sequence", "Element:Text"),
    #"Removed Other Columns2" = Table.SelectColumns(#"Pivoted Column",{"0", "1", "2", "3", "4", "5", "6", "7"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Other Columns2", each ([0] <> null)),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"VM Name", type text}, {"Location", type text}, {"Hypervisor Type", type text}, {"SLA Policy", type text}, {"Tags:", type any}, {"Backup Storage", type text}, {"Backup Size", type text}, {"Recovery Points", 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". Once you examined the code, replace the Source step with your own source.

[Raggsokk]

Thank you so much! ❤️

I used way to much time on this one 🙂