Forum Discussion
Is it possible to process EDI 835 or 837 files using Power Query?
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. 😀
