Import a text format file and convert it to 2 tables

[Anonymous]

Hi, I'm a newbie with the Power Query, I would like to import the file and convert the data to 2 tables, would you please tell me how to create the query? The file format is as blow: Capability measurement of the machine;EPS26-427 Date/Date;17/08/18;Time;17:31;Client/Customer;FASTPRINT R8 ;-34;+25;+270;+2,984900;+12,119472;+17,706148;+0,000000;+0,000000;1 R5 ;-118;+65; +0;+15,665836;+20,706369;+22,091282;+0,000000;+0,000000;1 R5 ;-118;+105; +0;-0,714882;+22,106749;+31,191521;+0,000000;+0,000000;4 R5 ;-118;+145; +0;-8,223645;+18,597939;+3,801935;+0,000000;+0,000000;7 R5 ;-118;+185; +0;-12,002868;+13,690780;-28,398226;+0,000000;+0,000000;10 R6 ;-90;+185;+90;-21,348570;+29,377335;+10,398713;+0,000000;+0,000000;1 R6 ;-90;+145;+90;-13,923892;+16,495628;+6,362763;+0,000000;+0,000000;4 R6 ;-90;+105;+90;-8,948331;+16,114807;-16,359098;+0,000000;+0,000000;7 R6 ;-90;+65;+90;-2,576315;+11,707122;-26,780851;+0,000000;+0,000000;10 R6 ;-90;+25;+90;-18,416435;+31,733908;-5,879363;+0,000000;+0,000000;1 There are more rows, I just deleted for example. I would like to convert it to 2 tables. Table 1: Capability measurement of the machine Date Time Client/Customer EPS26-427 17/08/2018 17:31 FASTPRINT Table2: Column1 Column2 Column3 Column4 Column5 Column6 Column7 Column8 Column9 Column10 R8 -34 25 270 2.984900 12.119472 17.706148 0 0 1 Would anybody tell me how to create the query for these requirements? Thank you a lot.

[edhans]

Can you provide sample data per instructions here? Your post just pastes as a single long 782 character text record.

[Anonymous]

Sorry, I attached the screenshot of the raw data. Thank you.

[Greg_Deckler]

So, just remove the top 2 rows. There is an option in the ribbon for that.

[edhans]

@Anonymous I'm not typing that in. Again, please see this post. We are trying to help, but it would take us hours to key that in and figure it out.

[Greg_Deckler]

@edhans I was able to create this from the first post and the screen shot:

Capability measurement of the machine;EPS26-427
Date/Date;17/08/18;Time;17:31;Client/Customer;FASTPRINT
R8;-34;+25;+270;+2,984900;+12,119472;+17,706148;+0,000000;+0,000000;1 
R5 ;-118;+65; +0;+15,665836;+20,706369;+22,091282;+0,000000;+0,000000;1 
R5 ;-118;+105; +0;-0,714882;+22,106749;+31,191521;+0,000000;+0,000000;4 
R5 ;-118;+145; +0;-8,223645;+18,597939;+3,801935;+0,000000;+0,000000;7 
R5 ;-118;+185; +0;-12,002868;+13,690780;-28,398226;+0,000000;+0,000000;10 
R6 ;-90;+185;+90;-21,348570;+29,377335;+10,398713;+0,000000;+0,000000;1 
R6 ;-90;+145;+90;-13,923892;+16,495628;+6,362763;+0,000000;+0,000000;4 
R6 ;-90;+105;+90;-8,948331;+16,114807;-16,359098;+0,000000;+0,000000;7 
R6 ;-90;+65;+90;-2,576315;+11,707122;-26,780851;+0,000000;+0,000000;10 
R6 ;-90;+25;+90;-18,416435;+31,733908;-5,879363;+0,000000;+0,000000;1 

[Greg_Deckler · Accepted solution]

OK, probably better ways to do this but PBIX is attached. You want Merge1. Basically wiped out all but the top 2 rows, converted everything to a single line. Then lots of misery and finally victory.

[Greg_Deckler]

Create 2 queries. Remove any unwanted columns and rows from each query.

[Anonymous]

@Greg_Deckler 

Thank you a lot, I can't convert the first 2 rows of the raw data to the table format showing as below, would you help me?

Capability measurement of the machine;EPS26-427
Date/Date;17/08/18;Time;17:31;Client/Customer;FASTPRINT

Table 1

[Anonymous]

Sorry about the format, it is mess, I almost couldn't post it, this web is not working normally without VPN from China.