Convert Xml CData to Table

Question

Hi,

I'm calling a web service that returns XML with a column of CData (commar delimted text) 

The query producing this is :

let
    Query2 = let
   Source = Xml.Tables(Web.Contents(etc..)
 
in
    Source,
    output = Query2{0}[output]
in
    output

The data returned is 1 big column of data representing the complete CSV data (Cdata).   I'm trying to find a way to push this data into the Csv.Document object so the I get a table back.n  I'm not sure of the Syntax. 

Can anyone give me a clue?

Thanks in advance.

Lee

Answer 1

I'm making some progress...

let 
Query2 = let   
Source = Xml.Tables(Web.Contents(etc..)

in    Source,     

output = Csv.Document(Query2{0}[output])  

in    output

I now get the Cdata in a table. 2 things are left. Promote the top row to column headers and change the types.

I think the keywords here are : Table.PromoteHeaders and Table.TransformColumnTypes.

This is a bit like making a jigsaw.

Is M based F# by any chance?   I need to understand the program flow and wondered if an F# book would help.

Answer 2

It's amazing how asking questions gets the mind over obstacles. 

I solved this.  Not sure if it's the best way and I don't fully understand it but here it is :

let
    Query2 = let
   Source = Xml.Tables(Web.Contents(etc..)) 
in
    Source,
	
    output = Csv.Document(Query2{0}[output]),
    
    RowAsHeader = Table.PromoteHeaders(output),
    
    NewTable = Table.TransformColumnTypes(
            RowAsHeader,
            {
               {"Column Name", type text}, 
               {"Column2 name", type text}, 
                 etc..
            }
    )

in
    NewTable

I think I'm going to have lots of fun learning M.