Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Import a text format file and convert it to 2 tables

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.
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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.

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Sorry about the format, it is mess, I almost couldn't post it, this web is not working normally without VPN from China.
  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
      Anonymous
      Not applicable
      6 years ago

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

       

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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.