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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
arpost
Kudo Collector
Kudo Collector

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
CharlotteCity12
Microsoft Employee
Microsoft Employee

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)
Anonymous
Not applicable

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

@Anonymous , 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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors