cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver II
Resolver II

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!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

4 REPLIES 4
Highlighted
Super User I
Super User I

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted

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 🙂

 

Highlighted
Resolver II
Resolver II

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

 

Highlighted

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors