Forum Discussion

Reine's avatar
Reine
Helper IV
1 year ago
Solved

Need some row data moved to columns?

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 i...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    1 year ago

    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

ryan_mayu's avatar
ryan_mayu
Super User
1 year ago

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