How to combine data importing from text

[Nantemate]

I have the longest files of this type of code:

MSH|^~\&|ECWExport|1256895402|TXImmTrac|TxDSHS|20230525082503.125-0500||VXU^V04^VXU_V04|3051312345678082603|P|2.5.1|||ER|AL|||||Z22^CDCPHINVS|1524685458|
PID|||789654^^^ECW^PI~^^^ECW^SS||TESTECW^JULIA^||20101207|F||2131-1^Other Race^HL70005|123 Phreesia rd^1033^Dallas^TX^70221^||^PRN^CP^^^999^9999999~^PRN^PH^^^777^7777777~^NET^^test.person@icloud.com^^^|||||||||2135-2^Hispanic or Latino^HL70189||||||||
PD1||||||||||||TXY|20230523|||A|||
NK1|1|TESTECW^JACK^|PAR^Parent^HL70063|123 Phreesia rd^^Dallas^TX^70221|^PRN^PH^^^777^7777777|||||||||||
ORC|RE|12345678^CDC|12345678^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20090930|20090930|20^DTaP (Infanrix) < 7 yrs^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^
OBX|1|CE|64994-7^Vaccine funding program eligibility category^LN|1|V02^VFC Eligible - Medicaid/Medicare^HL70064||||||F|||20230530
OBX|2|CE|30956-7^vaccine TYPE^LN|2|146^DTaP,IPV,Hib,HepB^CVX||||||F|||20230530
OBX|3|TS|29768-9^DATE vaccine information statement published^LN|2|20211015||||||F|||20230530
OBX|4|TS|29769-7^DATE vaccine information statement presented^LN|2|20230530||||||F|||20230530
MSH|^~\&|ECWExport|1256895402|TXImmTrac|TxDSHS|20230525082603.125-0500||VXU^V04^VXU_V04|3051312345679082703|P|2.5.1|||ER|AL|||||Z22^CDCPHINVS|1524685458|
PID|||789654^^^ECW^PI~^^^ECW^SS||TESTECW^NATALIA^||20101207|F||2131-1^Other Race^HL70005|123 Phreesia rd^1033^Dallas^TX^70221^||^PRN^CP^^^999^9999999~^PRN^PH^^^777^7777777~^NET^^test.person@icloud.com^^^|||||||||2135-2^Hispanic or Latino^HL70189||||||||
PD1||||||||||||TXY|20230523|||A|||
NK1|1|TESTECW^JACK^|PAR^Parent^HL70063|123 Phreesia rd^^Dallas^TX^70221|^PRN^PH^^^777^7777777|||||||||||
ORC|RE|12345679^CDC|12345679^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20090930|20090930|20^DTaP (Infanrix) < 7 yrs^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^
ORC|RE|12345680^CDC|12345680^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20060307|20060307|20^DTaP (Infanrix) < 7 yrs^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^
ORC|RE|12345681^CDC|12345681^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20050817|20050817|110^DTaP-Hep B-IPV*Pediarix (GSK)^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^
ORC|RE|12345682^CDC|12345682^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20050621|20050621|110^DTaP-Hep B-IPV*Pediarix (GSK)^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^
ORC|RE|12345683^CDC|12345683^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20050419|20050419|110^DTaP-Hep B-IPV*Pediarix (GSK)^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^
ORC|RE|12345684^CDC|12345684^CDC|||||||^^^^||^^^^^^^^^^^^|
RXA|0|1|20081203|20081203|83^Hep A (Havrix) 12 months- 19 yrs^CVX|999|||01^Historical information - source unspecified^NIP001|^^^|^^^||||||^^|^^||CP|A
RXR|^^|^^

Every message starts with a MSH| segment. What I wish to accomplish is to combine everything on the same message into one single row so I can divide it into columns afterwards.

Expected result in Excel:

Hope you can shed some light into what function must I learned. I have already tried searching, I promise!

Thank you

[slorin]

Hi, 

With Table.Group and GroupKind.Local

= Table.Group(
   Source,
   {"Column"},
   {{"Data", each Text.Combine([Column]), type nullable text}},
   GroupKind.Local, 
   (x,y)=> if Text.StartsWith(y[Column],"MSH") then 1 else 0)

 Stéphane

[edhans · Accepted solution]

Try the following code. Here is what I did:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"MSH","***MSH",Replacer.ReplaceText,{"Column1"}),
    Column1 = Text.Combine(#"Replaced Value"[Column1]),
    #"Split Text" = List.Select(Text.Split(Column1, "***"), each _ <> ""),
    #"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

When you split text, the splitter is removed, so I cannot split by MSH. The first thing I did is replaced MSH with ***MSH. Now I can split later by the *** chars.

The Column1 step combines all of that data into one massive block of text. It needs a list, and the #"Replaced Value"[Column1] returns everything as a list.

Then I use Text.Split and use the *** as my delimiter. I also wrapped that with List.Select() to remove any blank rows. the first row was blank for example.

Finally I converted to a table.

This is the result, loaded to Excel