Need some row data moved to columns?

[Reine]

Hello - I'm working with patient billing data.  If a patient has multiple charges on a bill it shows 1 row for each charge.  What I need to do is add columns for each charge on a bill so that there is one row per bill ID instead.  Any help is greatly appreciated.  

Link to download sample data:

https://medicaleyecenter-my.sharepoint.com/:x:/p/reine/ERNVHTWEa45Fkg9pZJZjSBMBs8jzSZnT6dx6k6OMez_xB... 

I'm attaching a very simplified version of sample data, but basically this is what it looks like now:

PG Data Before
and here's what I want it to look like:

PG Data After

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldRLroMwDAXQvTDuwHb8y1qq7n8bzyRQyEclT52g6ijmOjbv9+aSJOH22jIJAXE8EQCCikk8qyHY9nktQT8gZtMdYm4h9ZDjt8MEDaRMO9TsfEAgrCdSA9G1QCc8S7Nygd7B1J3IUk/sSv8XmgBUyJmoviPeoThSTY3pCwEnUI4wV+nE6GPDR8gmYjsEbqFZ2x7OqDAr3UOhbLV06mC5wmgKLEFHWITx7xlGmWZhVMb2wKw9A4yJKqVR75A1pxom7u2AcYcFWgOFoUv9G15D8QBvqf2YcGh2hhP38wjKNgkzQHYFm56IBcZlr8Hssgp1CVo8r8FruR5L47c9MmsPAffTA0DjPI6Q43XL9EADY5G0HYoHeAvzG1478wRvOyMO486glLm/h4lxTOMH4IQ1zOcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BillID = _t, #"Visit Date" = _t, PatientID = _t, ChargeCode = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BillID", Int64.Type}, {"Visit Date", Int64.Type}, {"PatientID", Int64.Type}, {"ChargeCode", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"BillID"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1)}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Visit Date", "PatientID", "ChargeCode", "Index"}, {"Visit Date", "PatientID", "ChargeCode", "Index"}),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Count", {{"Index", each "Charge Code " & Text.From(_, "en-IN"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Added Prefix", List.Distinct(#"Added Prefix"[Index]), "Index", "ChargeCode")
in
    #"Pivoted Column"

Hope this helps.

[Reine]

@ryan_mayu thank you so much.  I had not ever noticed or used the "group by" option in power query.  I think I can figure out how to make this work!  I'm running into some issues because my actual data has many more columns than the sample I sent and not all of those columns have identical data per BillID, but I will figure it out.  I truly appreciate your time helping me, thank you 🙂 

[ryan_mayu]

you are welcome

[ryan_mayu · Accepted solution]

you can try to do this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldRLroMwDAXQvTDuwHb8y1qq7n8bzyRQyEclT52g6ijmOjbv9+aSJOH22jIJAXE8EQCCikk8qyHY9nktQT8gZtMdYm4h9ZDjt8MEDaRMO9TsfEAgrCdSA9G1QCc8S7Nygd7B1J3IUk/sSv8XmgBUyJmoviPeoThSTY3pCwEnUI4wV+nE6GPDR8gmYjsEbqFZ2x7OqDAr3UOhbLV06mC5wmgKLEFHWITx7xlGmWZhVMb2wKw9A4yJKqVR75A1pxom7u2AcYcFWgOFoUv9G15D8QBvqf2YcGh2hhP38wjKNgkzQHYFm56IBcZlr8Hssgp1CVo8r8FruR5L47c9MmsPAffTA0DjPI6Q43XL9EADY5G0HYoHeAvzG1478wRvOyMO486glLm/h4lxTOMH4IQ1zOcP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BillID = _t, #"Visit Date" = _t, PatientID = _t, ChargeCode = _t]),
#"Grouped Rows" = Table.Group(Source, {"BillID", "Visit Date", "PatientID"}, {{"Charge Code", each Text.Combine([ChargeCode],",")}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "Charge Code", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Charge Code.1", "Charge Code.2", "Charge Code.3", "Charge Code.4", "Charge Code.5", "Charge Code.6"})
in
#"Split Column by Delimiter"

pls see the attahment below