Is Power Query right for my application? [Sample XML data provided]

D

dualhcsniatpac

Board Regular
Joined
Feb 18, 2009
Messages
126
Hello All,

I have been trying to find the best solution for what I am trying to achieve. I stumbled across Power Query and it piqued my curiosity. I don't use excel often but can usually fumble around and research to achieve what I need. What I hope to gain from this post is getting insight if Power Query is something I should keep researching or move on to the older schema/XML import tool.

What I am looking to achieve:
I will have .XML files that will be generated from my measurement equipment and I want to easily import, do calculations and display charts of the data. One requirement is that the import process is not difficult (for low-end users). All of the XML groups/headers will not change but the length of each table of data would change. For example, I could run a 10 piece study measuring 4 features each time. Next time I could do 100 cycles.

End-User Process:
User opens excel template or file. Points to XML file to import. Data is calculated. User reviews data. Saves the file and archives for future reference. It will always be XML file1 --> Excel File 1... XML file 2 --> Excel File 2 and so on. There might be situations where a user would want to group data from multiple XML files into a single Excel file but the DATA itself would never be merged together. Most likely multiple sheets to organize the data.

Where I am at now:
I started working on an excel spreadsheet that I copy and paste data to (from inside the software that drives the machine) and I am using formulas to process the data. I want to take the software out of the picture and be able to import all of the same information that is in the XML file. My current method has too many failure points such as the displayed units in the software. The excel file I have now works but has issues that I need to fix so it can handle more variation of data and prevent common errors. This led me to try out the XML import tool and at the same time discovered this new (to me) Power Query tool.

MY thoughts on using Power Query for my situation:
Pros:
  • I can program formatting upfront
  • More control of the data coming in. Almost like running a macro.
Cons:
  • This tool seems to be designed more towards querying the same thing over and over again and merging data together
  • I don't see myself needing a lot of the features of PQ, such as separating data based on a delimiter, merging information, etc.

* XML Sample Below - There is more data in the file but the repetitive stuff is removed.
There is 1 section that is repeated ("MeasurementPlan")

XML: 
<?xml version="1.0" encoding="utf-8"?>
<AutomeasureSession
  Version="1">
  <MeasurementPlan
    Version="1"
    Filename="F:\Measurement Plan.mp"
    Dirty="False">
    <Targets>
      <MeasurementPlanTarget
        Version="2"
        Name="RED"
        TargetType="ToolingBall"
        Az="0.2219902544778927"
        El="-0.24820302676878911"
        Range="3.6304608758955164"
        Radius="0.019050000000000001"
        Quality="124.72449802398681"
        Enabled="True"
        QualityThreshold="NaN"
        Stacking="1"
        Guid="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5" />
      <MeasurementPlanTarget
        Version="2"
        Name="BLUE"
        TargetType="ToolingBall"
        Az="-0.2097088478587458"
        El="-0.25407185607839888"
        Range="3.6024381639919518"
        Radius="0.019050000000000001"
        Quality="124.60943881988526"
        Enabled="True"
        QualityThreshold="NaN"
        Stacking="1"
        Guid="08d02554-33ed-4a42-892a-adcd2481a413" />
      <MeasurementPlanTarget
        Version="2"
        Name="GREEN"
        TargetType="ToolingBall"
        Az="0.00048069243978050288"
        El="-0.18803870292900215"
        Range="4.8068168166720149"
        Radius="0.019050000000000001"
        Quality="120.23147300720215"
        Enabled="True"
        QualityThreshold="NaN"
        Stacking="1"
        Guid="668431b3-2248-473c-9d3d-3b5ec27b45a5" />
      <MeasurementPlanTarget
        Version="2"
        Name="YELLOW"
        TargetType="ToolingBall"
        Az="0.00079303116834385711"
        El="0.084022664136877784"
        Range="3.8698394238310438"
        Radius="0.019050000000000001"
        Quality="123.45923700332642"
        Enabled="True"
        QualityThreshold="NaN"
        Stacking="1"
        Guid="c94d7a81-fbd8-4e20-a05c-dab422abc35b" />
    </Targets>
    <ScaleBarAssignments>
      <ScaleBarAssignment
        Version="1"
        Target1="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5"
        Target2="08d02554-33ed-4a42-892a-adcd2481a413">
        <ScaleBarAssignment
          Version="1"
          CustomName=""
          Color1="Blue"
          Color2="Red"
          Length="1.5009191000000002"
          Guid="d9d887b1-4f70-4526-a3d7-692c62c1c07e" />
      </ScaleBarAssignment>
      <ScaleBarAssignment
        Version="1"
        Target1="668431b3-2248-473c-9d3d-3b5ec27b45a5"
        Target2="c94d7a81-fbd8-4e20-a05c-dab422abc35b">
        <ScaleBarAssignment
          Version="1"
          CustomName=""
          Color1="Yellow"
          Color2="Green"
          Length="1.4987765"
          Guid="71921a01-dada-445a-90b8-4cb8ed2648f3" />
      </ScaleBarAssignment>
    </ScaleBarAssignments>
  </MeasurementPlan>
  <AutomeasureRunSettings
    Version="1"
    StoppingCondition="TotalIterations"
    RunDuration="01:00:00"
    RunIterations="30"
    TargetRepeat="1"
    PerformLinearizationAtStart="False"
    PerformSelfTestAtStart="False"
    PerformSelfTestEachIteration="False"
    StartMeasurementAtLastSuccessfulLocation="True"
    ContinueOnFailedMeasurement="True"
    EnvironmentRetrievalFrequency="00:00:10"
    FrameGrabPauseTime="00:00:00.5500000"
    PauseEachIteration="False"
    PauseEachIterationTime="00:00:00"
    SelfTestTime="00:00:00" />
  <AutomeasureRunData
    Version="1"
    StartTime="2021-04-30T10:24:10.6955435-04:00"
    StopTime="2021-04-30T10:30:40.0983706-04:00"
    Operator="VRSI"
    LRHostName="node"
    FirmwareVersion="MV"
    ToolkitVersion="10.5"
    SelfTestPassed="null"
    SelfTestResultsDetail="Passed"
    LinearizationPerformed="False"
    LinearityAtStart="0.0042922729626297951">
    <AutomeasureRunSettings
      Version="1"
      StoppingCondition="TotalIterations"
      RunDuration="01:00:00"
      RunIterations="30"
      TargetRepeat="1"
      PerformLinearizationAtStart="False"
      PerformSelfTestAtStart="False"
      PerformSelfTestEachIteration="False"
      StartMeasurementAtLastSuccessfulLocation="True"
      ContinueOnFailedMeasurement="True"
      EnvironmentRetrievalFrequency="00:00:10"
      FrameGrabPauseTime="00:00:00.5500000"
      PauseEachIteration="False"
      PauseEachIterationTime="00:00:00"
      SelfTestTime="00:00:00" />
    <MeasurementPlan
      Version="1"
      Filename="F:\Measurement Plan.mp"
      Dirty="False">
      <Targets>
        <MeasurementPlanTarget
          Version="2"
          Name="RED"
          TargetType="ToolingBall"
          Az="0.2219902544778927"
          El="-0.24820302676878911"
          Range="3.6304608758955164"
          Radius="0.019050000000000001"
          Quality="124.72449802398681"
          Enabled="True"
          QualityThreshold="NaN"
          Stacking="1"
          Guid="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5" />
        <MeasurementPlanTarget
          Version="2"
          Name="BLUE"
          TargetType="ToolingBall"
          Az="-0.2097088478587458"
          El="-0.25407185607839888"
          Range="3.6024381639919518"
          Radius="0.019050000000000001"
          Quality="124.60943881988526"
          Enabled="True"
          QualityThreshold="NaN"
          Stacking="1"
          Guid="08d02554-33ed-4a42-892a-adcd2481a413" />
      </Targets>
      <ScaleBarAssignments>
        <ScaleBarAssignment
          Version="1"
          Target1="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5"
          Target2="08d02554-33ed-4a42-892a-adcd2481a413">
          <ScaleBarAssignment
            Version="1"
            CustomName=""
            Color1="Blue"
            Color2="Red"
            Length="1.5009191000000002"
            Guid="d9d887b1-4f70-4526-a3d7-692c62c1c07e" />
        </ScaleBarAssignment>
      </ScaleBarAssignments>
    </MeasurementPlan>
    <ActualMeasurements>
      <MeasuredTarget
        Version="1"
        Az="0.22199175852392572"
        El="-0.24820056408205957"
        Range="3.6304616676530466"
        Quality="124.44815912246705"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5"
        Time="2021-04-30T10:24:14.1415887-04:00"
        Guid="20a9d71b-e2ff-4d14-a4a5-7ccf62115adb" />
      <MeasuredTarget
        Version="1"
        Az="-0.20970539972587821"
        El="-0.25407160983071159"
        Range="3.6024390606058692"
        Quality="124.44857418060303"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="08d02554-33ed-4a42-892a-adcd2481a413"
        Time="2021-04-30T10:24:17.9407538-04:00"
        Guid="1b7a97f0-7d9a-48e0-908e-28ddbd15067d" />
      <MeasuredTarget
        Version="1"
        Az="0.00048290703531805195"
        El="-0.18803967122525431"
        Range="4.8068176596417675"
        Quality="120.08717887878419"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="668431b3-2248-473c-9d3d-3b5ec27b45a5"
        Time="2021-04-30T10:24:21.8016686-04:00"
        Guid="a5410bdb-86a3-4b24-9335-7c0d5647b106" />
      <MeasuredTarget
        Version="1"
        Az="0.00079251901223918481"
        El="0.084024388565328623"
        Range="3.8698406944993136"
        Quality="123.1797643661499"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="c94d7a81-fbd8-4e20-a05c-dab422abc35b"
        Time="2021-04-30T10:24:25.8177340-04:00"
        Guid="ea214123-d21d-4e7b-9bbf-b427b7f4aa81" />
      <MeasuredTarget
        Version="1"
        Az="0.22199355576350494"
        El="-0.24820020092712813"
        Range="3.6304624255202387"
        Quality="124.40802669525146"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5"
        Time="2021-04-30T10:24:29.6921159-04:00"
        Guid="b5e7e9ff-8231-4568-a4ac-d83fae357daf" />
      <MeasuredTarget
        Version="1"
        Az="-0.20970497874099628"
        El="-0.2540720030337677"
        Range="3.6024393506664318"
        Quality="124.60624347686768"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="08d02554-33ed-4a42-892a-adcd2481a413"
        Time="2021-04-30T10:24:33.0976816-04:00"
        Guid="00af5bf9-f837-4b85-b9e1-f8247f8f62bd" />
      <MeasuredTarget
        Version="1"
        Az="0.00048318747328147466"
        El="-0.18804114300163047"
        Range="4.806818104738344"
        Quality="120.18183521270753"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="668431b3-2248-473c-9d3d-3b5ec27b45a5"
        Time="2021-04-30T10:24:36.5890801-04:00"
        Guid="7e7df39d-935b-4c40-8890-8d490726308a" />
      <MeasuredTarget
        Version="1"
        Az="0.00079244406623929736"
        El="0.084023227540309164"
        Range="3.8698403868678306"
        Quality="123.18487405776978"
        Radius="0.019050000000000001"
        ResultCode="NONE"
        NominalTarget="c94d7a81-fbd8-4e20-a05c-dab422abc35b"
        Time="2021-04-30T10:24:39.7474187-04:00"
        Guid="5c306e42-e743-4ab8-a7bd-8b3a8ba3458a" />
    </ActualMeasurements>
    <EnvironmentData
      Version="1">
      <Sensors>
        <SensorData
          Sensor="AirPressure">
          <AMDataSeries
            Version="1"
            Sum="31357.362752407789"
            SSQ="30727631.263514087"
            Count="32"
            StandardDeviation="0.040738635418746089"
            Min="979.84664291143417"
            Max="979.99310493469238"
            Range="0.14646202325820923">
            <Values>
              <Value
                Time="2021-04-30T10:24:11.0050059-04:00"
                Value="979.95649054646492" />
              <Value
                Time="2021-04-30T10:24:22.7905193-04:00"
                Value="979.9381822347641" />
            </Values>
          </AMDataSeries>
        </SensorData>
        <SensorData
          Sensor="TemperatureAir">
          <AMDataSeries
            Version="1"
            Sum="657.44121134281158"
            SSQ="13507.282585768142"
            Count="32"
            StandardDeviation="0.064260469501834921"
            Min="20.448921918869015"
            Max="20.644204616546627"
            Range="0.1952826976776123">
            <Values>
              <Value
                Time="2021-04-30T10:24:11.0050059-04:00"
                Value="20.458686351776127" />
              <Value
                Time="2021-04-30T10:24:22.7905193-04:00"
                Value="20.458686351776127" />
            </Values>
          </AMDataSeries>
        </SensorData>
        <SensorData
          Sensor="TemperatureHead">
          <AMDataSeries
            Version="1"
            Sum="797.35919189453125"
            SSQ="19868.205970675801"
            Count="32"
            StandardDeviation="0.030290311145888427"
            Min="24.860254287719727"
            Max="24.945703506469727"
            Range="0.08544921875">
            <Values>
              <Value
                Time="2021-04-30T10:24:11.0050059-04:00"
                Value="24.860254287719727" />
              <Value
                Time="2021-04-30T10:24:22.7905193-04:00"
                Value="24.860254287719727" />
            </Values>
          </AMDataSeries>
        </SensorData>
    </EnvironmentData>
    <TargetVideoImages>
      <VideoFrame
        Guid="b8bdc229-568e-41bb-b0a1-ed7351f3c7f5">/9j/4AAQSk......... </VideoFrame>
        <VideoFrame
        Guid="08d02554-33ed-4a42-892a-adcd2481a413">/9j/4AAQSkZJRg......... </VideoFrame>
        <VideoFrame
        Guid="668431b3-2248-473c-9d3d-3b5ec27b45a5">/9j/4AAQSkZJRgABAQEAYA......... </VideoFrame>
        <VideoFrame
        Guid="c94d7a81-fbd8-4e20-a05c-dab422abc35b">/9j/4AAQSkZJRgABA......... </VideoFrame>
        </TargetVideoImages>
  </AutomeasureRunData>
  <OutputViewerFormSettings
    Version="1">
    <Charts />
  </OutputViewerFormSettings>
</AutomeasureSession>
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top