Forum Discussion

DannyP's avatar
DannyP
New Member
3 years ago
Solved

Converting data from XML file into columns

Hi, I would like to import a dataset from a xml file into Powerquery. I'm fairly new to Powerquery but suspect it's a nested table. In screenshot1 you can see the data after import. The table 'CURVE...
Need Help
  • ams1's avatar
    ams1
    3 years ago

    Hi,

     

    GIVEN your sample file,

     

    WHEN

     

    let
    Source = Xml.Tables(File.Contents("ADD_YOUR_PATH_TO_FILE\sample.xml")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CURVEDATA", type text}}),
    CurveData = #"Changed Type"{0}[CURVEDATA],
    csv = Csv.Document(CurveData,[Delimiter="#(tab)", Columns=6]),
    #"Removed Top Rows" = Table.Skip(csv,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type text}, {"Relaxation Time", type text}, {"TPL Curve", type text}, {"TPU Curve", type text}, {"Viscosity", type text}, {"Relaxation", type text}})
in
    #"Changed Type1"

     

     

    THEN

     

    If you want you can remove the first row with the units of measure.

     

    Please mark this reply as answer if it helped.

ams1's avatar
ams1
Responsive Resident
3 years ago

Hi,

 

GIVEN your sample file,

 

WHEN

 

let
    Source = Xml.Tables(File.Contents("ADD_YOUR_PATH_TO_FILE\sample.xml")),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CURVEDATA", type text}}),
    CurveData = #"Changed Type"{0}[CURVEDATA],
    csv = Csv.Document(CurveData,[Delimiter="#(tab)", Columns=6]),
    #"Removed Top Rows" = Table.Skip(csv,1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type text}, {"Relaxation Time", type text}, {"TPL Curve", type text}, {"TPU Curve", type text}, {"Viscosity", type text}, {"Relaxation", type text}})
in
    #"Changed Type1"

 

 

THEN

 

If you want you can remove the first row with the units of measure.

 

Please mark this reply as answer if it helped.

DannyP's avatar
DannyP
New Member
3 years ago

Thanks. That worked for me and can start building the query.