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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
arpost
Advocate V
Advocate V

Is it possible to process EDI 835 or 837 files using Power Query?

I need to process a large number of 835 and 837 files and am wondering if Power Query can do the trick.

 

Here's an example from BlueCross (sample details can be found on pgs. 12-13) that shows raw, sample data:

 

arpost_0-1628211771049.png

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZK9jp0wEIVfxdryFFfjPzDpjOEKEsDEsCs2qy1S7XaRIqVI42fPALqbK6WIhQvLM+c7c/DLy8OywmkLqbTJ9ZzQQ1ZKXVyBgNB9wd1SJCVJcnlNEyRIlRWV1sEW5CpTkcmpveKqMITBG+2a3KwjDNlbp8mTBDPq4bEVIcVlEX5qxHFcur4dGhGvYopp7UTwKQ795POkMYso6rgJpSqZJ4PmMXV+xBSgypJUntuEsGFt4YistcaZE9Vie/4mutYPa8eCDI1pjsmvfZywbaDKlYU1Wsl7DPfslND5NMSVVXeQ2wPa51s/H2HZonRVHjbIHIaZJyT+qsJ4qzgbJYk+gpRGXayBtKiMtJwER0F5GiW+Bq5tfvx6e8f4/efvPeSxh3ie6w8EFx8xkqVbjDovTwFd+GQroxnDMEiy5c5ivfNC0pl+qfY2Yj026heECKOgzaU4jvw32K8s2eAxXZFAO9KPK+piv7EXc8czBPYEV9j/o1hbQ9k7XXWnpIrzVZVsZ9+Q/6r87dQ3R1yalxZan0/24fX1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Sample = _t]),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Sample", Splitter.SplitTextByDelimiter("~", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Sample"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sample", type text}})
in
    #"Changed Type1"

 

 

For now, I'd like to just get it so that the column structure could look something like this but across all columns without requiring I rename columns or have duplicate columns for duplicate rows (e.g., CAS_1.1, CAS_2.1):

arpost_1-1628212220876.png

 

N1.1N1.2N1.3N1.4N1.5
PEXYZ Healthcare CorporationXX987654321 
PRBlue Cross and Blue Shield nullnullnull
3 REPLIES 3

HIPAA ANSI 834 File Layout:
Has anyone used PBI to transform a HIPAA ANSI 834 TXT File Layout into rows and columns for reporting? See Sample Data:  834 Example (Benefit Enrollment and Maintenance Transaction)EDI Blog | EDI Blog (ediacademy.com)
watkinnc
Super User
Super User

My friend, Power Query is perfect for 835/837 files. I wish everybody would listen!!


You can filter to just the lines that start with "N1" using Table.SelectRows(TableName, each Text.StartsWith([ColumnName], "N1"))

 

From there it's just a matter of using the built in functions, like SplitByDelimiter using "*", choosing the columns to keep, add some new header names, all is well!

 

I tend to simplify, but Power Query is THE PERFECT TOOL for these files.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

@watkinnc , thanks for the reply! That's what I thought too, and I have the delimiter split happening, but what I didn't realize was that EDIs are actually hierarchical, so certain rows may be 3 levels deep in the hierarchy and be part of a loop, so there isn't a quick-and-easy record identifier! 😢 I figure this might need to involve some stair-stepping across columns and then fill downs or conditional logical fills.

 

Microsoft has some docs that summarize aspects of this structure: https://docs.microsoft.com/en-us/biztalk/core/edi-message-structure

 

I'm hoping someone in the community has explored this before and had some brilliant ideas. 😀

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors