locked
Jagged levels within XML

    Question

  • Hi,

    I have an XML data source that is returning a jagged number of levels/attributes.

    DE is not interpretting this correctly as is coming across a level (table) alongside a single row attribute.  

    With the formula :

    = Table.TransformColumnTypes(level3,{{"Attribute:id", type text}, {"Attribute:name", type text}, {"Attribute:currency", type text}, {"Attribute:abbr", type text}})

    I receive the error :

    Expression.Error: Cannot convert a value of type Table to type Text. Details: Value=Table, Type=Type

    This is because the jagged nature of the XML gives a table of attributes where DE is expecting only Attributes.

    Is there a function available that will give me a flat table?

    Thanks in advance.

    Friday, June 14, 2013 1:57 PM

All replies

  • Hi Lee,

    Could you please share some example xml for this issue?

    Regards

    Qunshu


    Clarification: Microsoft doesn't own any liability & responsibility for any of my posting.

    Saturday, June 15, 2013 3:47 PM
  • <?xml version="1.0"?>
    <response success="true">
    	<output>
    		<levels>
    			<level id="1" name="Anon" currency="USD" abbr="">
    				<level id="61" name="Anon" currency="USD" abbr="">
    					<level id="642" name="Anon" currency="USD" abbr="">
    						<level id="141" name="Anon" currency="USD" abbr="">
    							<level id="153" name="Anon" currency="USD" abbr=""/>
    							<level id="154" name="Anon" currency="USD" abbr=""/>
    							<level id="185" name="Anon" currency="USD" abbr=""/>
    							<level id="186" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="142" name="Anon" currency="USD" abbr="">
    							<level id="155" name="Anon" currency="USD" abbr=""/>
    							<level id="159" name="Anon" currency="USD" abbr=""/>
    							<level id="163" name="Anon" currency="USD" abbr=""/>
    							<level id="188" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="143" name="Anon" currency="USD" abbr="">
    							<level id="156" name="Anon" currency="USD" abbr=""/>
    							<level id="157" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="144" name="Anon" currency="USD" abbr="">
    							<level id="158" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="145" name="Anon" currency="USD" abbr="">
    							<level id="160" name="Anon" currency="USD" abbr=""/>
    							<level id="175" name="Anon" currency="USD" abbr=""/>
    							<level id="176" name="Anon" currency="USD" abbr=""/>
    							<level id="180" name="Anon" currency="USD" abbr=""/>
    							<level id="181" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="146" name="Anon" currency="USD" abbr="">
    							<level id="162" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="147" name="Anon" currency="USD" abbr="">
    							<level id="662" name="Anon" currency="USD" abbr=""/>
    							<level id="164" name="Anon" currency="USD" abbr=""/>
    							<level id="165" name="Anon" currency="USD" abbr=""/>
    							<level id="166" name="Anon" currency="USD" abbr=""/>
    							<level id="167" name="Anon" currency="USD" abbr=""/>
    							<level id="177" name="Anon" currency="USD" abbr=""/>
    							<level id="183" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="148" name="Anon" currency="USD" abbr="">
    							<level id="168" name="Anon" currency="USD" abbr=""/>
    							<level id="171" name="Anon" currency="USD" abbr=""/>
    							<level id="172" name="Anon" currency="USD" abbr=""/>
    							<level id="173" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="149" name="Anon" currency="USD" abbr="">
    							<level id="169" name="Anon" currency="USD" abbr=""/>
    							<level id="174" name="Anon" currency="USD" abbr=""/>
    							<level id="178" name="Anon" currency="USD" abbr=""/>
    							<level id="179" name="Anon" currency="USD" abbr=""/>
    							<level id="182" name="Anon" currency="USD" abbr=""/>
    							<level id="184" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="150" name="Anon" currency="USD" abbr="">
    							<level id="170" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="151" name="Anon" currency="USD" abbr="">
    							<level id="187" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="152" name="Anon" currency="USD" abbr="">
    							<level id="189" name="Anon" currency="USD" abbr=""/>
    							<level id="190" name="Anon" currency="USD" abbr=""/>
    							<level id="191" name="Anon" currency="USD" abbr=""/>
    						</level>
    					</level>
    					<level id="121" name="Anon" currency="USD" abbr="">
    						<level id="63" name="Anon" currency="USD" abbr=""/>
    						<level id="201" name="Anon" currency="USD" abbr="">
    							<level id="468" name="Anon" currency="USD" abbr="">
    								<level id="624" name="Anon" currency="USD" abbr=""/>
    								<level id="479" name="Anon" currency="USD" abbr=""/>
    								<level id="480" name="Anon" currency="USD" abbr=""/>
    								<level id="481" name="Anon" currency="USD" abbr=""/>
    								<level id="482" name="Anon" currency="USD" abbr=""/>
    								<level id="483" name="Anon" currency="USD" abbr=""/>
    								<level id="484" name="Anon" currency="USD" abbr=""/>
    								<level id="485" name="Anon" currency="USD" abbr=""/>
    								<level id="486" name="Anon" currency="USD" abbr=""/>
    								<level id="487" name="Anon" currency="USD" abbr=""/>
    								<level id="488" name="Anon" currency="USD" abbr=""/>
    								<level id="489" name="Anon" currency="USD" abbr=""/>
    								<level id="490" name="Anon" currency="USD" abbr=""/>
    								<level id="491" name="Anon" currency="USD" abbr=""/>
    								<level id="492" name="Anon" currency="USD" abbr=""/>
    								<level id="493" name="Anon" currency="USD" abbr=""/>
    								<level id="494" name="Anon" currency="USD" abbr=""/>
    								<level id="495" name="Anon" currency="USD" abbr=""/>
    								<level id="627" name="Anon" currency="USD" abbr=""/>
    								<level id="497" name="Anon" currency="USD" abbr=""/>
    								<level id="499" name="Anon" currency="USD" abbr=""/>
    								<level id="500" name="Anon" currency="USD" abbr=""/>
    								<level id="501" name="Anon" currency="USD" abbr=""/>
    								<level id="502" name="Anon" currency="USD" abbr=""/>
    								<level id="503" name="Anon" currency="USD" abbr="">
    									<level id="557" name="Anon" currency="USD" abbr=""/>
    									<level id="558" name="Anon" currency="USD" abbr=""/>
    									<level id="559" name="Anon" currency="USD" abbr=""/>
    									<level id="560" name="Anon" currency="USD" abbr=""/>
    									<level id="561" name="Anon" currency="USD" abbr=""/>
    								</level>
    								<level id="504" name="Anon" currency="USD" abbr=""/>
    								<level id="505" name="Anon" currency="USD" abbr=""/>
    								<level id="506" name="Anon" currency="USD" abbr=""/>
    								<level id="507" name="Anon" currency="USD" abbr=""/>
    								<level id="508" name="Anon" currency="USD" abbr=""/>
    								<level id="509" name="Anon" currency="USD" abbr=""/>
    								<level id="510" name="Anon" currency="USD" abbr=""/>
    								<level id="511" name="Anon" currency="USD" abbr=""/>
    								<level id="512" name="Anon" currency="USD" abbr=""/>
    								<level id="513" name="Anon" currency="USD" abbr=""/>
    								<level id="514" name="Anon" currency="USD" abbr=""/>
    								<level id="515" name="Anon" currency="USD" abbr=""/>
    								<level id="516" name="Anon" currency="USD" abbr=""/>
    								<level id="517" name="Anon" currency="USD" abbr=""/>
    								<level id="518" name="Anon" currency="USD" abbr=""/>
    								<level id="601" name="Anon" currency="USD" abbr=""/>
    								<level id="682" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="469" name="Anon" currency="USD" abbr="">
    								<level id="621" name="Anon" currency="USD" abbr=""/>
    								<level id="625" name="Anon" currency="USD" abbr=""/>
    								<level id="470" name="Anon" currency="USD" abbr="">
    									<level id="562" name="Anon" currency="USD" abbr=""/>
    									<level id="563" name="Anon" currency="USD" abbr=""/>
    									<level id="564" name="Anon" currency="USD" abbr=""/>
    									<level id="565" name="Anon" currency="USD" abbr=""/>
    									<level id="566" name="Anon" currency="USD" abbr=""/>
    								</level>
    								<level id="471" name="Anon" currency="USD" abbr="">
    									<level id="567" name="Anon" currency="USD" abbr=""/>
    									<level id="568" name="Anon" currency="USD" abbr=""/>
    									<level id="569" name="Anon" currency="USD" abbr=""/>
    									<level id="570" name="Anon" currency="USD" abbr=""/>
    									<level id="581" name="Anon" currency="USD" abbr=""/>
    								</level>
    								<level id="472" name="Anon" currency="USD" abbr=""/>
    								<level id="473" name="Anon" currency="USD" abbr=""/>
    								<level id="474" name="Anon" currency="USD" abbr=""/>
    								<level id="475" name="Anon" currency="USD" abbr=""/>
    								<level id="476" name="Anon" currency="USD" abbr=""/>
    								<level id="477" name="Anon" currency="USD" abbr=""/>
    								<level id="478" name="Anon" currency="USD" abbr=""/>
    								<level id="683" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="204" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="64" name="Anon" currency="USD" abbr="">
    							<level id="241" name="Anon" currency="USD" abbr="">
    								<level id="389" name="Anon" currency="USD" abbr=""/>
    								<level id="390" name="Anon" currency="USD" abbr=""/>
    								<level id="401" name="Anon" currency="USD" abbr=""/>
    								<level id="402" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="281" name="Anon" currency="USD" abbr="">
    								<level id="519" name="Anon" currency="USD" abbr=""/>
    								<level id="520" name="Anon" currency="USD" abbr=""/>
    								<level id="521" name="Anon" currency="USD" abbr=""/>
    								<level id="522" name="Anon" currency="USD" abbr=""/>
    								<level id="523" name="Anon" currency="USD" abbr=""/>
    								<level id="524" name="Anon" currency="USD" abbr=""/>
    								<level id="525" name="Anon" currency="USD" abbr=""/>
    								<level id="526" name="Anon" currency="USD" abbr=""/>
    								<level id="527" name="Anon" currency="USD" abbr=""/>
    								<level id="528" name="Anon" currency="USD" abbr="">
    									<level id="721" name="Anon" currency="USD" abbr=""/>
    									<level id="722" name="Anon" currency="USD" abbr=""/>
    								</level>
    								<level id="529" name="Anon" currency="USD" abbr=""/>
    								<level id="530" name="Anon" currency="USD" abbr=""/>
    								<level id="531" name="Anon" currency="USD" abbr=""/>
    								<level id="532" name="Anon" currency="USD" abbr=""/>
    								<level id="533" name="Anon" currency="USD" abbr=""/>
    								<level id="534" name="Anon" currency="USD" abbr=""/>
    								<level id="622" name="Anon" currency="USD" abbr=""/>
    								<level id="623" name="Anon" currency="USD" abbr=""/>
    								<level id="626" name="Anon" currency="USD" abbr=""/>
    								<level id="535" name="Anon" currency="USD" abbr=""/>
    								<level id="536" name="Anon" currency="USD" abbr=""/>
    								<level id="537" name="Anon" currency="USD" abbr=""/>
    								<level id="538" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="684" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="66" name="Anon" currency="USD" abbr="">
    							<level id="539" name="Anon" currency="USD" abbr=""/>
    							<level id="540" name="Anon" currency="USD" abbr=""/>
    							<level id="541" name="Anon" currency="USD" abbr=""/>
    							<level id="542" name="Anon" currency="USD" abbr=""/>
    							<level id="543" name="Anon" currency="USD" abbr=""/>
    							<level id="544" name="Anon" currency="USD" abbr="">
    								<level id="552" name="Anon" currency="USD" abbr=""/>
    								<level id="781" name="Anon" currency="USD" abbr=""/>
    								<level id="782" name="Anon" currency="USD" abbr=""/>
    								<level id="783" name="Anon" currency="USD" abbr=""/>
    								<level id="784" name="Anon" currency="USD" abbr=""/>
    								<level id="785" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="545" name="Anon" currency="USD" abbr="">
    								<level id="606" name="Anon" currency="USD" abbr=""/>
    								<level id="607" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="546" name="Anon" currency="USD" abbr=""/>
    							<level id="547" name="Anon" currency="USD" abbr=""/>
    							<level id="548" name="Anon" currency="USD" abbr=""/>
    							<level id="549" name="Anon" currency="USD" abbr=""/>
    							<level id="550" name="Anon" currency="USD" abbr="">
    								<level id="553" name="Anon" currency="USD" abbr=""/>
    								<level id="786" name="Anon" currency="USD" abbr=""/>
    								<level id="787" name="Anon" currency="USD" abbr=""/>
    								<level id="788" name="Anon" currency="USD" abbr=""/>
    								<level id="789" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="551" name="Anon" currency="USD" abbr=""/>
    							<level id="628" name="Anon" currency="USD" abbr=""/>
    							<level id="554" name="Anon" currency="USD" abbr=""/>
    							<level id="555" name="Anon" currency="USD" abbr="">
    								<level id="602" name="Anon" currency="USD" abbr=""/>
    								<level id="603" name="Anon" currency="USD" abbr=""/>
    							</level>
    							<level id="556" name="Anon" currency="USD" abbr=""/>
    							<level id="685" name="Anon" currency="USD" abbr=""/>
    							<level id="701" name="Anon" currency="USD" abbr=""/>
    							<level id="761" name="Anon" currency="USD" abbr=""/>
    						</level>
    						<level id="67" name="Anon" currency="USD" abbr=""/>
    						<level id="742" name="Anon" currency="USD" abbr=""/>
    					</level>
    				</level>
    				<level id="62" name="Anon" currency="USD" abbr="">
    					<level id="641" name="Anon" currency="USD" abbr="">
    						<level id="68" name="Anon" currency="USD" abbr="">
    							<level id="261" name="Anon" currency="USD" abbr=""/>
    							<level id="262" name="Anon" currency="USD" abbr=""/>
    							<level id="686" name="Anon" currency="USD" abbr=""/>
    						</level>
    					</level>
    					<level id="643" name="Anon" currency="USD" abbr="">
    						<level id="161" name="Anon" currency="USD" abbr=""/>
    					</level>
    				</level>
    			</level>
    		</levels>
    	</output>
    </response>
    
    

    Tuesday, June 18, 2013 9:27 AM
  • I think this is a bug.

    The query for the table giving the error is :

    let
        Source = Xml.Tables(Web.Contents(https://server,[Content=Text.ToBinary("<?xml version='1.0' encoding='UTF-8'?><call method=''><credentials login='' password=''></credentials></call>")])),
        output = Source{0}[output],
        levels = output{0}[levels],
        level = levels{0}[level],
        ChangedType = Table.TransformColumnTypes(level,{{"Attribute:id", type number}, {"Attribute:name", type text}, {"Attribute:currency", type text}, {"Attribute:abbr", type text}}),
        level1 = ChangedType{0}[level],
        ChangedType1 = Table.TransformColumnTypes(level1,{{"Attribute:id", type number}, {"Attribute:name", type text}, {"Attribute:currency", type text}, {"Attribute:abbr", type text}}),
        level2 = ChangedType1{0}[level],
        ChangedType2 = Table.TransformColumnTypes(level2,{{"Attribute:id", type number}, {"Attribute:name", type text}, {"Attribute:currency", type text}, {"Attribute:abbr", type text}}),
        level3 = ChangedType2{1}[level],
        ChangedType3 = Table.TransformColumnTypes(level3,{{"Attribute:id", type text}, {"Attribute:name", type text}, {"Attribute:currency", type text}, {"Attribute:abbr", type text}}),
        #"Attribute:id" = ChangedType3{3}[#"Attribute:id"]
    in
        #"Attribute:id"

    If I amend the ChangedType3 variable by changing {"Attribute:id", type text} to {"Attribute:id", type table} I get an error saying table not recognised, this was just a guess.  

    When I step back to the previous view the levels are displayed with no error, the tables are displayed with the navigation link and the single levels are displayed as an attribute.   I can click through the jagged levels succesfully.   Very odd.

    If start the query from the top and drill down through the levels the tables at level 3 are displayed as Error.   This only seems to happen when there are single level nodes at the same level as multi-level nodes.

    I appreciate this is pre-production software so I'm not too distressed by this.

    Tuesday, June 18, 2013 12:35 PM
  • Hi Lee,

    Thanks for reporting this. We have noticed this behavior and we are improving the experience for it.

    Regards

    Qunshu


    Clarification: Microsoft doesn't own any liability & responsibility for any of my posting.

    Tuesday, June 18, 2013 7:46 PM