Need help with tables in Power Query

AlertMtl

New Member
Joined
Feb 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Needing some help combining columns in power query that either contains table data or "Null". These are laser cut files that I need to extract attributes from ex. material, thickness and so on. There seem to be two formats, attached are 2 files opened in notepad, for these files (close to 15K). I will attach a screenshot of where I'm at in Power query. I was able to make a sample file for each format but unsure what to do from here.
 

Attachments

  • COMBINECOLUMNS.png
    COMBINECOLUMNS.png
    203.6 KB · Views: 7
  • 2VARIATIONS.png
    2VARIATIONS.png
    197.6 KB · Views: 6

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.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
560
Office Version
  1. 365
Platform
  1. Windows
Can you run the query for each source separately and then append?
 

herr_morty

New Member
Joined
Jul 26, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Sorry, not sure what you need to do with the data. Can you please specify a bit more?

If you need all the data from the #2 "Transform data" columns in one column, I think you could use a new conditional column (e.g., if column 1 is not null, than column 1; if column 1 is null, than column 2; else null)

Let us know :)
 

AlertMtl

New Member
Joined
Feb 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I need to get all the data together in the attached format to be able to have it migrated into new software. I am very new to this so forgive my ignorance...I'm not sure exactly sure how the merge and append works. Although the 2 different formats look to be very similar the problem is when you click on table they open differently. Notice the screenshots attached for each format, needed data is in both but different route to get to it
 

Attachments

  • WANTEDEXCELDATA.png
    WANTEDEXCELDATA.png
    174.9 KB · Views: 3
  • TANSFORMSAMPLE1.png
    TANSFORMSAMPLE1.png
    166.4 KB · Views: 3
  • TRANSFORMSAMPLE2.png
    TRANSFORMSAMPLE2.png
    191.6 KB · Views: 3

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

I suggest to post links to these two xml files shared via onedrive, googledrive, dropbox or any similar
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
I forgot to say you need change path to the files for your own
 

AlertMtl

New Member
Joined
Feb 14, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
@sandy666 THANK YOU FOR HELPING WITH THIS...I EDITED THE FILE YOU SENT TO SHOW THE FORMAT IM LOOKING FOR. THERES A SCREENSHOT ATTACHED SHOWING THE 29K PLUS FILES THAT IM NEEDING THE DATA FROM...HOW CAN I APPLY THIS QUERY TO THE WHOLE FOLDER? THANKS AGAIN FOR THE TIME
 

Attachments

  • CADFOLDER.png
    CADFOLDER.png
    209.9 KB · Views: 3

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
Could STOP SCREAMING? Please?

I will need three files more for testing
 

Watch MrExcel Video

Forum statistics

Threads
1,133,427
Messages
5,658,732
Members
418,467
Latest member
sc356448

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
Top