Create bins or groups as a column

Question

My goal is below to have a patient ID and then what catorgies or DX that are assoicated to their chart.&nbsp;Patient&nbsp;DiabetesCHFAsthmaSleep Apnea&nbsp;xxxx1XX&nbsp;Xxxxx2&nbsp;XX&nbsp;xxxx3X&nbsp;&nbsp;&nbsp;xxxx4XXXXxxxx5&nbsp;&nbsp;XXxxxx6&nbsp;X&nbsp;&nbsp;xxxx7XX&nbsp;&nbsp;xxxx8XXXXxxxx9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;What im getting&nbsp;Patient&nbsp;DiabetesCHFAsthmaSleep Apnea&nbsp;xxxx1X&nbsp;&nbsp;&nbsp;xxxx1&nbsp;x&nbsp;&nbsp;xxxx1&nbsp;&nbsp;&nbsp;xxxxx2&nbsp;X&nbsp;&nbsp;xxxx2&nbsp;&nbsp;X&nbsp;xxxx3x&nbsp;&nbsp;&nbsp;xxxx4x&nbsp;&nbsp;&nbsp;xxxx4&nbsp;x&nbsp;&nbsp;xxxx4&nbsp;&nbsp;x&nbsp;xxxx4&nbsp;&nbsp;&nbsp;x&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I am trying to avoid creating a table for each DX because it will just grow. My data is in three tables.&nbsp;Table one- Patient DataTable two- Every possible DX codetable three- Appointment encounters.&nbsp;&nbsp;Im at a lost now i feel like this should be a simple table to construct.&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
Please try below operations in Power Query Pane:
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqgACQyUdpQggVoDjWB2EDEikAqeMAlwFTMYIKhKBoccISX0Eiowxih3IMiZ4ZbC7zQTFXbhkoK6OBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Patient " = _t, Diabetes = _t, CHF = _t, Asthma = _t, #"Sleep Apnea " = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Patient ", type text}, {"Diabetes", type text}, {"CHF", type text}, {"Asthma", type text}, {"Sleep Apnea ", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Patient "}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value", List.Max)
in
    #"Pivoted Column"

Best regards,Community Support Team_Binbin YuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Create bins or groups as a column

1 Reply

Related content

Create columns group

Grouping rows by Column and then updating Column

Grouping Columns

Fabric & Azure Data User Group Program Requirements & Benefits

Matrix column not grouped

Patient	Diabetes	CHF	Asthma	Sleep Apnea
xxxx1	X	X		X
xxxx2		X	X
xxxx3	X
xxxx4	X	X	X	X
xxxx5			X	X
xxxx6		X
xxxx7	X	X
xxxx8	X	X	X	X
xxxx9