Power Query Struggle with XML

[et1mark]

I have a Form fill PDF that I'm trying to extract the data from in Power Query. I'm currently using the .FDF and using acrobat to extract the data but I want to swap to XML and use Power Query to table it. However, the XML isn't populating correctly in PQ. If I try to gather data from more than one XML, the data runs off into one long massive column instead of a separate column for each file. Please watch the video below to see what I'm talking about.

Excel Concierge Service — Mozilla Firefox

Use Loom to record quick videos of your screen and cam. Explain anything clearly and easily – and skip the meeting. An essential tool for hybrid workplaces.

www.loom.com

What am I doing wrong???

 

[Alex Blakenburg]

Your data look remarkable clean.
Try this:-

• Once you get to having just the 2 columns, change the headings to something more manageable, say Description and Value
• Filter on the first (Description) column if you need to. It can't have any nulls in it, so exclude nulls
• Add a conditional column if Description = 02 Date then pull get the Date from the value column
• In the new column Fill Down.
• With the 1st Description column highlighted Pivot with Value being the Value column
  Expand Advanced and select don't aggregate.
• You can delete the extra date column
• Select all columns (ctrl+A) transform > detect data types
• Eye ball the data types to make sure they are ok and Close & Load (to)

Hopefully I got that right, my test data was only slightly different to this.

 

[et1mark]

Your data look remarkable clean.
Try this:-

• Once you get to having just the 2 columns, change the headings to something more manageable, say Description and Value
• Filter on the first (Description) column if you need to. It can't have any nulls in it, so exclude nulls
• Add a conditional column if Description = 02 Date then pull get the Date from the value column
• In the new column Fill Down.
• With the 1st Description column highlighted Pivot with Value being the Value column
  Expand Advanced and select don't aggregate.
• You can delete the extra date column
• Select all columns (ctrl+A) transform > detect data types
• Eye ball the data types to make sure they are ok and Close & Load (to)

Hopefully I got that right, my test data was only slightly different to this.

View attachment 39231

Alex, I appreciate the compliment. And holy smokes did you just teach me some kung fu! I did have to add a second conditional column to futher separate records occurring on the same date but that worked amazingly well!

 

[Alex Blakenburg]

Loved your feedback, you've made my day. Kudos to you for working out the extra steps for making the rows unique.