Forum Discussion
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 'CURVEDATA' contains the data I need. How do I turn that dataset into a table with columns? Should I post a part of the xml in this post?
Thanks in advance.
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.
4 Replies
Hi,
Yes, please post a sample for the XML and the query you're using.
Don't forget to remove confidential information from the XML and query!
This is de data in the XML file:
<?xml version='1.0'?> <TEST> <UNITS> </UNITS> <HEADER> <SAMPLEIDENTIFICATION> </SAMPLEIDENTIFICATION> </HEADER> <CURVEDATA> Time Relaxation Time TPL Curve TPU Curve Viscosity Relaxation m.m min C C MU MU 4.8000 NAN 125.000 125.000 48.330 NAN 4.8167 NAN 125.000 125.000 48.310 NAN 4.8333 NAN 125.000 125.000 48.300 NAN 4.8500 NAN 125.000 125.000 48.280 NAN 4.8667 NAN 125.000 125.000 48.270 NAN 4.8833 NAN 125.000 125.000 48.260 NAN 4.9000 NAN 125.000 125.000 48.240 NAN 4.9167 NAN 125.000 125.000 48.220 NAN 4.9333 NAN 125.000 125.000 48.180 NAN 4.9500 NAN 125.000 125.000 48.160 NAN 4.9667 NAN 125.000 125.000 48.130 NAN 4.9833 NAN 125.000 125.000 48.100 NAN 5.0000 NAN 125.000 125.000 48.100 NAN 5.0033 0.003 125.000 125.000 47.170 47.170 5.0067 0.007 125.000 125.000 38.930 38.930 5.0100 0.010 125.000 125.000 32.160 32.160 5.0133 0.013 125.000 125.000 28.410 28.410 5.0167 0.017 125.000 125.000 25.880 25.880 5.0200 0.020 125.000 125.000 23.970 23.970 5.0233 0.023 125.000 125.000 22.470 22.470 5.0267 0.027 125.000 125.000 21.250 21.250 5.0300 0.030 125.000 125.000 20.230 20.230 5.0333 0.033 125.000 125.000 19.360 19.360 5.0367 0.037 125.000 125.000 18.610 18.610 5.0400 0.040 125.000 125.000 17.940 17.940 5.0433 0.043 125.000 125.000 17.360 17.360 </CURVEDATA> <SUBTEST> <SETPOINTS> </SETPOINTS> <SIMPLEDATA> </SIMPLEDATA> </SUBTEST> <DAISYRESULTS> </DAISYRESULTS> </TEST>I can't see how to attach a file but within power query this is the generated dax formula after import with the whole dataset of curvedata in one row:
= Table.TransformColumnTypes(Source,{{"CURVEDATA", type text}})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.
Thanks. That worked for me and can start building the query.
