Forum Discussion
Solved
HIPAA ANSI 834 File Layout:
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)
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.
1 Reply
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.