Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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)
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors