none
PowerQuery bug?

    Question

  • The following is a partial extract from a REST API call.  The ASSET portion in the XML correctly shows up as a table in PowerQuery, but when you expand it, the column data don't line up correctly for the two ASSET records.

    I see that in the first ASSET record, the <PARENT> field is not included (the REST API does not include them if it is NULL). But it is included in the second ASSET record.  And, this throws PowerQuery off.

    = = = = = = =

    <?xml version="1.0" encoding="UTF-8"?>
    <QueryREP_LOCATIONSResponse xmlns="http://www.ibm.com/maximo" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" creationDateTime="2013-09-21T18:46:40-07:00" transLanguage="EN" baseLanguage="EN" messageID="1379814400337849607" maximoVersion="7 5 20130129-2023 V7504-38" rsStart="0">
    <REP_LOCATIONSSet>
        <LOCATIONS>
            <AUTOWOGEN>0</AUTOWOGEN>
            <CHANGEBY>MAXIMO</CHANGEBY>
            <CHANGEDATE>1999-03-11T16:53:00-08:00</CHANGEDATE>
            <DESCRIPTION>Boiler Room Reciprocating Compressor</DESCRIPTION>
            <DISABLED>0</DISABLED>
            <GLACCOUNT>
                <VALUE>6210-326-???</VALUE>
            </GLACCOUNT>
            <ISDEFAULT>0</ISDEFAULT>
            <ISREPAIRFACILITY>0</ISREPAIRFACILITY>
            <LOCATION>BR300</LOCATION>
            <LOCATIONSID>190</LOCATIONSID>
            <ORGID>EAGLENA</ORGID>
            <PLUSCLOOP>0</PLUSCLOOP>
            <PLUSCPMEXTDATE>0</PLUSCPMEXTDATE>
            <SITEID>BEDFORD</SITEID>
            <STATUS>OPERATING</STATUS>
            <STATUSDATE>1999-03-11T16:53:00-08:00</STATUSDATE>
            <TYPE>OPERATING</TYPE>
            <USEINPOPR>0</USEINPOPR>
            <ASSET>
                <ASSETID>12</ASSETID>
                <ASSETNUM>11300</ASSETNUM>
                <ASSETTAG>4286</ASSETTAG>
                <ASSETUID>118</ASSETUID>
                <AUTOWOGEN>0</AUTOWOGEN>
                <BUDGETCOST>3000.0</BUDGETCOST>
                <CALNUM>COMPANY1</CALNUM>
                <CHANGEBY>WILSON</CHANGEBY>
                <CHANGEDATE>2005-02-07T10:15:13-08:00</CHANGEDATE>
                <CHILDREN>1</CHILDREN>
                <DESCRIPTION>Reciprocating Compressor- Air Cooled/100 CFM</DESCRIPTION>
                <DISABLED>0</DISABLED>
                <INSTALLDATE>1994-05-31T00:00:00-07:00</INSTALLDATE>
                <INVCOST>0.0</INVCOST>
                <ISCALIBRATION>0</ISCALIBRATION>
                <ISLINEAR>0</ISLINEAR>
                <ISRUNNING>1</ISRUNNING>
                <ITEMSETID>SET1</ITEMSETID>
                <MAINTHIERCHY>0</MAINTHIERCHY>
                <MANUFACTURER>IR</MANUFACTURER>
                <MOVED>0</MOVED>
                <ORGID>EAGLENA</ORGID>
                <PLUSCISCONTAM>0</PLUSCISCONTAM>
                <PLUSCISINHOUSECAL>0</PLUSCISINHOUSECAL>
                <PLUSCISMTE>0</PLUSCISMTE>
                <PLUSCPMEXTDATE>0</PLUSCPMEXTDATE>
                <PLUSCSOLUTION>0</PLUSCSOLUTION>
                <PRIORITY>5</PRIORITY>
                <PURCHASEPRICE>37500.0</PURCHASEPRICE>
                <REPLACECOST>58000.0</REPLACECOST>
                <RETURNEDTOVENDOR>0</RETURNEDTOVENDOR>
                <SERIALNUM>43960</SERIALNUM>
                <STATUS>NOT READY</STATUS>
                <STATUSDATE>1995-05-30T00:00:00-07:00</STATUSDATE>
                <TLOAMPARTITION>0</TLOAMPARTITION>
                <TOTALCOST>27491.0</TOTALCOST>
                <TOTDOWNTIME>0.0</TOTDOWNTIME>
                <TOTUNCHARGEDCOST>0.0</TOTUNCHARGEDCOST>
                <UNCHARGEDCOST>0.0</UNCHARGEDCOST>
                <VENDOR>IR</VENDOR>
                <WARRANTYEXPDATE>1999-05-29T00:00:00-07:00</WARRANTYEXPDATE>
                <YTDCOST>4991.0</YTDCOST>
            </ASSET>
            <ASSET>
                <ASSETID>137</ASSETID>
                <ASSETNUM>11340</ASSETNUM>
                <ASSETTAG>4285</ASSETTAG>
                <ASSETUID>137</ASSETUID>
                <AUTOWOGEN>0</AUTOWOGEN>
                <BUDGETCOST>400.0</BUDGETCOST>
                <CALNUM>COMPANY1</CALNUM>
                <CHANGEBY>WILSON</CHANGEBY>
                <CHANGEDATE>2005-02-07T10:15:39-08:00</CHANGEDATE>
                <CHILDREN>0</CHILDREN>
                <DESCRIPTION>Motor Starter- Size 4/NEMA 12/440v/3ph/60hz</DESCRIPTION>
                <DISABLED>0</DISABLED>
                <INSTALLDATE>1994-05-31T00:00:00-07:00</INSTALLDATE>
                <INVCOST>0.0</INVCOST>
                <ISCALIBRATION>0</ISCALIBRATION>
                <ISLINEAR>0</ISLINEAR>
                <ISRUNNING>1</ISRUNNING>
                <ITEMSETID>SET1</ITEMSETID>
                <MAINTHIERCHY>0</MAINTHIERCHY>
                <MANUFACTURER>WES</MANUFACTURER>
                <MOVED>0</MOVED>
                <ORGID>EAGLENA</ORGID>
                <PARENT>11300</PARENT>
                <PLUSCISCONTAM>0</PLUSCISCONTAM>
                <PLUSCISINHOUSECAL>0</PLUSCISINHOUSECAL>
                <PLUSCISMTE>0</PLUSCISMTE>
                <PLUSCPMEXTDATE>0</PLUSCPMEXTDATE>
                <PLUSCSOLUTION>0</PLUSCSOLUTION>
                <PRIORITY>4</PRIORITY>
                <PURCHASEPRICE>2406.0</PURCHASEPRICE>
                <REPLACECOST>4700.0</REPLACECOST>
                <RETURNEDTOVENDOR>0</RETURNEDTOVENDOR>
                <SERIALNUM>23-4630</SERIALNUM>
                <STATUS>NOT READY</STATUS>
                <STATUSDATE>1995-05-30T00:00:00-07:00</STATUSDATE>
                <TLOAMPARTITION>0</TLOAMPARTITION>
                <TOTALCOST>0.0</TOTALCOST>
                <TOTDOWNTIME>0.0</TOTDOWNTIME>
                <TOTUNCHARGEDCOST>0.0</TOTUNCHARGEDCOST>
                <UNCHARGEDCOST>0.0</UNCHARGEDCOST>
                <VENDOR>WES</VENDOR>
                <WARRANTYEXPDATE>1999-05-29T00:00:00-07:00</WARRANTYEXPDATE>
                <YTDCOST>0.0</YTDCOST>
            </ASSET>

    = = = = = = =

    I tried to manually edit the TransformColumnTypes to include the PARENT column, but PowerQuery throws an error saying that it already exists.

    Sunday, September 22, 2013 4:46 PM

Answers

All replies

  • Apologies for the delay in answering your question, but could you please look at the end of your table? The table is first built using the columns in the first entry and then if additional fields are found in subsequent entries, they get appended at the end of the field list. When I tried your repro I did see the Parent column at the far right of the table in Power Query.
    Monday, October 14, 2013 9:00 PM
    Moderator
  • Regardless, the values in the other columns do not line up correctly in the spreadsheet.
    Tuesday, October 15, 2013 1:24 AM
  • Could you please point out where the values don't line up and/or publish a screenshot? I used the data in your post and loaded it into Power Query but it looks ok to me.
    Thursday, October 17, 2013 11:39 PM
    Moderator
  • The screen shot is already in the first post as the Query2 spreadsheet.  The values don't lineup after the ORGID column.

    • Edited by aargeekay Friday, October 18, 2013 12:32 AM
    Friday, October 18, 2013 12:31 AM
  • Ok I do see it in your post, but that's not what I'm seeing with our latest build. What version are you using? (It's displayed in the About box.) You can find our latest preview bits for V2 at http://www.microsoft.com/en-us/download/details.aspx?id=39933

    Assuming you're running an earlier build, could you please try this one and see if it resolves your issue? If you are already using this then we'll need to debug some more so I can repro it locally.

    Friday, October 18, 2013 4:26 AM
    Moderator