Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Nantemate
Regular Visitor

How to combine data importing from text

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:

 

Nantemate_0-1686067173515.png

 

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

 

Thank you

 

 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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

 

edhans_0-1686070814291.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
slorin
Super User
Super User

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
Super User
Super User

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

 

edhans_0-1686070814291.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors