Search code examples
excelpowerquery

Convert part of column data to row eg one ID has many appointments with different diagnoses. Change diagnosis row to columns

I have a list of study IDs in an Excel file who some have a single encounters and some with duplicate encounters with different diagnoses as shown in the example below. I would like if possible to split the file to show the diagnoses as rows but not sure how to do this if it is possible using Power Query or another way using Excel. Can anyone help please.

example

Solution

  • Option 1 - Muliple diagnosis merged into one cell

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STUDY_ID", type text}, {"ENCNTR_ID", Int64.Type}, {"DIAGNOSIS_DISPLAY", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"STUDY_ID", "ENCNTR_ID"}, {{"Rows", each _, type table [STUDY_ID=nullable text, ENCNTR_ID=nullable number, DIAGNOSIS_DISPLAY=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "DIAGNOSIS_DISPLAY", each Text.Combine([Rows][DIAGNOSIS_DISPLAY], "#(lf)"), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"})
in
    #"Removed Columns"

    Option 2 - Muliple diagnosis into separate columns

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STUDY_ID", type text}, {"ENCNTR_ID", Int64.Type}, {"DIAGNOSIS_DISPLAY", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"STUDY_ID", "ENCNTR_ID"}, {{"Rows", each _ }}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn(Table.AddIndexColumn([Rows], "Index", 1, 1), "Diagnosis", each "DIAGNOSIS " & Number.ToText([Index]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Rows"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"DIAGNOSIS_DISPLAY", "Diagnosis"}, {"DIAGNOSIS_DISPLAY", "Diagnosis"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Diagnosis]), "Diagnosis", "DIAGNOSIS_DISPLAY")
in
    #"Pivoted Column"