cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arpost
Advocate III
Advocate III

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
Microsoft

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors