Converting data from XML file into columns

[DannyP]

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.

[DannyP]

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}})

[ams1 · Accepted solution]

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]

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

[ams1]

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!