Forum Discussion
Need some row data moved to columns?
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 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 🙂
you are welcome