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

[arpost]

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:

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):

N1.1	N1.2	N1.3	N1.4	N1.5
PE	XYZ Healthcare Corporation	XX	987654321
PR	Blue Cross and Blue Shield	null	null	null

[CharlotteCity12]

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]

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

[arpost]

@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. 😀