transform jagged data stream to a single cell

[Richard_U]

I'm trying to import a PDF file and I get as far as trimming it down to the data I want.  The data is delimited, but the rows run into the next, making the delimiters useless.

example:
DATA

48***01*021000021**9102716876*20180824\TRN*1*1337738\REF*TN*0001337738\REF*GX*02

063686900000063\DTM*009*20180824\N1*PR*REDACTED, LL\N1*PE*REDACTED

(REDACTED CONT.)\ENT*1\RMR*IV*REDACTED28001AABA018**52900*52900\DTM*003*20180419\RMR*IV*R

EDACTED28001AAYF3UP**140400*140400\DTM*003*20180508\RMR*IV*REDACTED28001AAW91XK**154100*

154100\DTM*003*20180603\RMR*IV*REDACTED28001ABOO82Y**315900*315900\DTM*003*20180724\

RMR*IV*REDACTED28001AAZX80L**227000*227000\DTM*003*20180612\RMR*IV*REDACTED28001AB69U9U*

*302400*302400\DTM*003*20180612\RMR*IV*REDACTED28001ABOOUUU**245500*245500\DTM*003*2

0180729\RMR*IV*REDACTED28001ABOOS5T**237200*237200\DTM*003*20180724\RMR*IV*REDACTED28001

ABOOCTT**276200*276200\DTM*003*20180722\RMR*IV*REDACTED28001ABONUAJ**177600*177600\D

TM*003*20180725\RMR*IV*REDACTED28001ABOO9SY**237200*237200\DTM*003*20180724\RMR*IV*S

UDU28001ABOOEKH**299800*299800\DTM*003*20180724\RMR*IV*REDACTED28001ABOOV0D**299800*

299800\DTM*003*20180724\RMR*IV*REDACTED28001ABOO1MB**299800*299800\DTM*003*20180724\

RMR*IV*REDACTED28001ABOO3UY**237200*237200\DTM*003*20180724\RMR*IV*REDACTED28001ABOORRG*

*237200*237200\DTM*003*20180724\RMR*IV*REDACTED28001ABOO2H0**147900*147900\DTM*003*2

0180724\RMR*IV*REDACTED28001ABSK32X**255500*255500\DTM*003*20180729\RMR*IV*REDACTED28001

ABZEMM7**968400*968400\DTM*003*20180729\RMR*IV*REDACTED28001ABONZJU**563600*563600\D

TM*003*20180727\RMR*IV*REDACTEDA8GDYZQ0155XC**5400*5400\DTM*003*20180518\RMR*IV*REDACTED

28001ABGF042**174600*174600\DTM*003*20180706\RMR*IV*REDACTED28001ABCJNVT**234200*234

200\DTM*003*20180703\RMR*IV*REDACTED28001ABEF048**206500*206500\DTM*003*20180622\RMR

*IV*REDACTED28001AAUBVZL/A**34200*34200\DTM*003*20180523\RMR*IV*REDACTED28001AB3JTKL/A**

23000*23000\DTM*003*20180609\RMR*IV*REDACTED28001AAW91XK/A**15500*15500\DTM*003*2018

0603\RMR*IV*REDACTED28001AAYFSTJ/A**34200*34200\DTM*003*20180530\RMR*IV*REDACTED28001AAY

F3UP/A**34200*34200\DTM*003*20180530\RMR*IV*REDACTED28001AB1YPVL/A**10000*10000\DTM*

003*20180530\RMR*IV*REDACTED28001AB8Q05U/A**10000*10000\DTM*003*20180614\RMR*IV*REDACTED

28001AB69U9U/A**26200*26200\DTM*003*20180612\RMR*IV*REDACTED28001AAYEP5K/A**25000*25

000\DTM*003*20180530\RMR*IV*REDACTED28001AATOZBL/A**26200*26200\DTM*003*20180524\RMR

*IV*REDACTED28001AAUBAHE/A**34200*34200\DTM*003*20180523\RMR*IV*REDACTED28001ABZBGL3**20

8700*208700\DTM*003*20180728\RMR*IV*REDACTED28001ABCIJS7**183900*183900\DTM*003*2018

0628\RMR*IV*REDACTED28001ABZBS4P**612400*612400\DTM*003*20180728\RMR*IV*REDACTED28001ABZ

B3Y4**918600*918600\DTM*003*20180728\RMR*IV*REDACTED28001ABZEKHY**524100*524100\DTM*

003*20180729\RMR*IV*REDACTED28001ABSKW0F**406200*406200\DTM*003*20180729\RMR*IV*REDACTED

28001ABLD5F1**497100*497100\DTM*003*20180708\RMR*IV*REDACTED28001ABSK1UG**812400*812

400\DTM*003*20180729\RMR*IV*REDACTED28001ABIQKU4**291700*291700\DTM*003*20180630\SE*

98*000000063\GE*000001*020636869\IEA*00001*020636869\

Is there any way I can combine this into a single cell, then parse it out by the delimiters?

[v-angzheng-msft · Accepted solution]

Hi, @Richard_U 

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVfbdqMwDPyVnjzt6vTs+ortR0hImnsaIA2U/v9vrCzTNgXcJc0D03CisSyPRu7r60xZAGAcmOAMP4IDOM6E4Yk1CQjGLbNCtW15PgAHLqUx0rbtOV9CeQCM+PJqdUWi2dvj64wlMrGJY/RJZNsuyj3+3N1QHjicznDOF+m8zBePD7tdeJd/vCOmX+/fHubHQ/nnd9vmhxI4Lrg/w/ry8WNhMZk0zVJcAEALXDs839eWYW3F3WcsrfCVoF7K6gTAFVPIEKBHoZmNLf/i+HWL0VphPYHow989ioTJCEV2PFpRA0iu/RYC9KKNrx+Rj2fRXC3bAQhhsPod9BPgIpZA4ipXhdxBMuHLEGA6xfFYVRUmoLT2CRDcRAeJ0EZcnKLQJVJIIzwFwVgZxqKJ31PMS09hEqIgGFDE93Co0g0epTGJFwIBRhP3Vwod34Mr6ul7KIi7WlQf4fn2CcOdsz6cYGoJfPSFLT6iifp+Cr7PJiQQlyJSyOqOEgyiz+dVJ8UfU4gn5vvZOOpn42JSjFIUWymuuAcd1Kx7av5WyJ0Um3y/N+itifXtFGAqhZdis8F20miq3tUIxqVoBhSpXS3q5plxra9z5PDr6xFP40NPC5oJKayWTAnfDSp0g0qGG2BJbAPzzeFCzazCEapAPUIRtcUcE0BjFyyhQyDoG1LXzEEvQ1usskuz+5uit1Ia9OyXQUQTkJtyS9Ehd0nWKkeclcXOMUwHT8FJSXwopG54RsuA46koN//Zg2TRaOL3E+6nFBmvTxcqA10YwjNEh9wnUNhnpqtRipvZMuzGWznSjPIUInj7iLXHx1Na5ydN5yA0naLuUr+jkuWxyXbfJ6A7R4nLMX3KfyzHJlvtpO8HoreGmsLDwFZil5Vsvt4UaErcSrJGglE5ximarFB4W8Jae18JMD2BhvgzWSt/7bTeWQLcQYEjsvb3PbpwBRiT4zfuWmxf2BJAMTrJABO8+VaOu4Ve4tVZOeOzCDCwtugeii2vVgA2FBGBqO+aEOvnbaX8pOYkBIJ+P3gxF3moi7PAPi/nqzx88/8HdDf3tl3nKQxezt7e/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),


    
    Custom1 = Table.TransformColumns(#"Changed Type",{"Column1", each Text.Combine(#"Changed Type"[Column1])}),
    #"Removed Duplicates" = Table.Distinct(Custom1)
in
    #"Removed Duplicates"

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[watkinnc]

Try

= Text.Combine(Table[Data])

--Nate