Forum Discussion

Reine's avatar
Reine
Icon for Helper IV rankHelper 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

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
1 year ago

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.