Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
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
@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. 😀
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
66 | |
27 | |
18 | |
12 |