Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Raggsokk
Frequent Visitor

Need help to convert XML file to PowerBI report

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.

Raggsokk_0-1709118154634.png

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

 

lbendlin_0-1709146117326.png

 

 

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.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

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.

 

lbendlin_0-1709146117326.png

 

 

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.

Thank you so much! ❤️

I used way to much time on this one 🙂

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors