HIPAA ANSI 834 File Layout:

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

[lbendlin · Accepted solution]

You could start like this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZHdbsIwDIVfpeLyaBd20qTJZWkDRKOho2UaQrz/a8wdjLYw7edmUeQL+8T2+XI6LboeTucgIpZgiOlNKSp5cX46LT7Ccp2kAoYilksWEgwT5OSjbB9W0A5cEBdmTNd9C00atft8X4zFl/6Ifgc1ZhKjNahjl8Ixi6nCKsIbbTU562aymLCsll120V51hWWlJ7vH1OEIdiBNMh/lfF+Sawz5wf2keSPdt2K42R26gCZWzzJAzsDpUd6GPWILbFoUnNvCk/d+0k2DjTXXPbdlqrN6H18DukPsQ8azyTnKVG5CbFCV8MrRBE3drAeKLGlmsdSMpU09GLyDnruvoQ/GeTt8lCPTzKwMuOSz+Ttc9kdcKcVww/Ug/3dcWggoi9VfcPF90d+KjvWvWAoK5XHxfn4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> "")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "Column1", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}, {"Column1.9", Int64.Type}, {"Column1.10", Int64.Type}})
in
    #"Changed Type1"

and then add a mapping table for the values in the first two columns.  Then it would be a question of separating the sbu entries in case you have a structure like in the example.