Forum Discussion

I_Like_Pi's avatar
I_Like_Pi
Icon for Resolver II rankResolver II
9 years ago
Solved

Importing multi-line txt records

I am trying to import a multiline text record formated as follows:

ITEM          VALUE

ITEM...........: Apple
SAttribute1..: 123
SAttribute2..: ABC
SAttribute3..: ZYX
MAttribute...: asdf
MAttribute...: qwer
MAttribute...: zxcv

ITEM...........: Banana
SAttribute1..: 654
SAttribute2..: CBA
SAttribute3..: XYZ
MAttribute...: asdf
MAttribute...: qwer

MAttribute...: zxcv

...

 

How do I import them to; 

ITEM      SAttribute1      SAttribute2      SAttribute3      MAttribute

Apples       123                ABC                  ZYX                 asdf,qwer,zxcv

Banana      321                CBA                  XYZ                 asdf,qwer,zxcv

 

Note that the source includes those dots to the fixed length position and those would be nice to remove. I was able to get the 2 columns @ the fixed location. I have no idea how to "flatten" (i know thats the wrong term) this.  I am pretty sure every record includes the same 6 lines of attributes. HELP!!

 

Need Help

4 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Are you using an imported model?  In power query you can merge columns together, is this in a column format?

    • I_Like_Pi's avatar
      I_Like_Pi
      Icon for Resolver II rankResolver II
      9 years ago

      Sorry I am not understanding your question. I suppose the merge might be the second part of my question if that is the part you are answering. But before I get there I would need to get here:

       

      ITEM      SAttribute1      SAttribute2      SAttribute3      MAttribute        MAttribute      MAttribute

      Apples       123                ABC                  ZYX                 asdf                     qwer                zxcv

      Banana      321                CBA                  XYZ                 asdf                     qwer                zxcv

       

      How do I first "flatten" the file?

      Here is my source line if that helps:

      let
      Source = Csv.Document(File.Contents("C:\path\srvtble.txt"),null,{0,40},null,1252),

      ...

       

      Thanks for your time :)

       

  • I_Like_Pi's avatar
    I_Like_Pi
    Icon for Resolver II rankResolver II
    9 years ago

    I have added an index and a subsequent Rec number using a calculated column.

    RecNo = Number.RoundUp([Index]/7)

    I feel close but I still cant figure out how to flatten this.

     

    ITEM          VALUE   Index   RecNo

    ITEM...........: Apple      1          1
    SAttribute1..: 123      2          1
    SAttribute2..: ABC      3          1
    SAttribute3..: ZYX      4          1

    MAttribute...: asdf      5          1
    MAttribute...: qwer      6          1
    MAttribute...: zxcv      7          1

    ITEM...........: Banana     8          2
    SAttribute1..: 654      9          2
    SAttribute2..: CBA      10          2
    SAttribute3..: XYZ      11          2
    MAttribute...: asdf      12          2
    MAttribute...: qwer      13          2

    MAttribute...: zxcv      14          2