Unpivoting multiple pairs of columns into unique rows

[ExcelUser]

Hi All,

**I can upload the sample Excel file, but I don't see an option to do that. The below image is what I see**

I have sample data that uses 1 row per ID number, and multiple "pairs" of columns: Diagnosis & it's Influence (ICD_10CCMCCFlag#). Each ID number usually has up to 50 of these Diagnosis/Influence columns.

Fake ID Number	ICD10_Diagnosis1	ICD10_CCMCCFlag1	ICD10_Diagnosis2	ICD10_CCMCCFlag2	ICD10_Diagnosis3	ICD10_CCMCCFlag3
1	e8779 - other fluid overload	No Influence	j9601 - acute respiratory failure with hypoxia	Diagnosis MCC	g9341 - metabolic encephalopathy	Diagnosis MCC
2	e43 - unspecified severe protein-calorie malnutrition	No Influence	u071 - covid-19	Diagnosis MCC	i10 - essential (primary) hypertension	No Influence
3	e8770 - fluid overload, unspecified	No Influence	n186 - end stage renal disease	Diagnosis MCC	e1122 - type 2 diabetes mellitus with diabetic chronic kidney disease	No Influence
4	e8720 - acidosis, unspecified	No Influence	b20 - human immunodeficiency virus [hiv] disease	Diagnosis CC	g9341 - metabolic encephalopathy	Diagnosis MCC
5	e8770 - fluid overload, unspecified	No Influence	j9621 - acute and chronic respiratory failure with hypoxia	Diagnosis MCC	e1122 - type 2 diabetes mellitus with diabetic chronic kidney disease	No Influence

I would like to unpivot these pairs into unique rows, where each row has 1 ID number and 1 of its Diagnosis/Influence:

Fake ID Number	Diagnosis	Diagnosis Influence
1	e8779 - other fluid overload	No Influence
1	j9601 - acute respiratory failure with hypoxia	Diagnosis MCC
1	g9341 - metabolic encephalopathy	Diagnosis MCC
2	e43 - unspecified severe protein-calorie malnutrition	No Influence
2	u071 - covid-19	Diagnosis MCC
2	i10 - essential (primary) hypertension	No Influence
3	e8770 - fluid overload, unspecified	No Influence
3	n186 - end stage renal disease	Diagnosis MCC
3	e1122 - type 2 diabetes mellitus with diabetic chronic kidney disease	No Influence
4	e8720 - acidosis, unspecified	No Influence
4	b20 - human immunodeficiency virus [hiv] disease	Diagnosis CC
4	g9341 - metabolic encephalopathy	Diagnosis MCC
5	e8770 - fluid overload, unspecified	No Influence
5	j9621 - acute and chronic respiratory failure with hypoxia	Diagnosis MCC
5	e1122 - type 2 diabetes mellitus with diabetic chronic kidney disease	No Influence

Right now I'm manually merging all the pairs together, unpivoting them, and then splitting by deliminator. But the real dataset has upwards of 50 pairs. Manually doing this is very time-consuming.

Is there a more elegant solution I'm missing?

Thank you!

Dan

[ronrsnfld]

Paste the code below into the Advanced Editor of Power Query

Edit the 2nd line to reflect your actual data source

You may need to change the name (and possibly the data type) for "Column1" where it is referenced in the code.

Read the code comments and examine the Applied Steps to better understand the algorithm used.

let

//change next line to reflect actual table source
    Source = Excel.CurrentWorkbook(){[Name="Table15"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Zip({Table.ColumnNames(Source), {Int64.Type} & List.Repeat({type text}, Table.ColumnCount(Source)-1)})),

//Unpivot all EXCEPT first column
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {Table.ColumnNames(Source){0}}, "Attribute", "Value"),

//Create column to enable Grouping by "Pairs"
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Integer-Division", each Number.IntegerDivide([Index], 2), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Integer-Division",{"Index"}),

//Group by pairs
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Integer-Division"}, {
        //aggregate into new one row table

        {"Result", each Record.FromList( {[Fake ID Number]{0}} & [Value],{"Fake ID Number","Diagnois","Diagnosis Influence"}), type record}
        }),

//remove Grouper column and expand the rest
    #"Removed Columns1" = Table.RemoveColumns(#"Grouped Rows",{"Integer-Division"}),
    #"Expanded Result" = Table.ExpandRecordColumn(#"Removed Columns1", "Result",{"Fake ID Number", "Diagnois", "Diagnosis Influence"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Result",{{"Fake ID Number", Int64.Type}, {"Diagnois", type text}, {"Diagnosis Influence", type text}})
in
    #"Changed Type1"

Results from your data

[AlanFredes · Accepted solution]

Hi Dan,

it is possible to do this using only Power Query.

using the example data you provided I built an example file that transforms the data in Power Query.

Link to file: Unpivoting multiple pairs of columns into unique rows.pbix

In the future, if you want to share a files you will need to use onedrive or googledrive and provide the link in your post.

Did I answer your question? Mark my post as a solution!

Regards,

Alan

[ExcelUser]

Thanks for helping out with this problem. I can follow your attachment and replicate everything on my end. 

In the future I'll know I need to upload sample file in onedrive so it can be opend by everyone.

[Excelquestor]

Please follow the below instructions and let me know if it works out : 

1. First, you need to get your data into Power Query. It's pretty simple. Just go to the "Data" tab in Excel and click on "From Table/Range".

2. Once your data is in Power Query, click on the header of the "Fake ID Number" column to select it. Now, go to the "Transform" tab, click on "Unpivot Columns", and then choose "Unpivot Other Columns". This action will transform all the columns, except for the "Fake ID Number", into a more manageable format.

3. After doing this, you'll see three columns: "Fake ID Number", "Attribute", and "Value". The "Attribute" column will have the original column headers, and the "Value" column will have the corresponding values.

4. Now, you'll notice that the "Attribute" column has both the diagnosis and its influence combined. We need to separate these into two columns. To do this, click on the "Attribute" column, go to the "Transform" tab, and click on "Split Column". Choose "By Delimiter", and in the box that pops up, select "Custom" and type "_" as the delimiter. Make sure to split at the "Right-most delimiter". This will divide the "Attribute" column into two separate columns: one for the diagnosis and one for its influence.

5. The final step is to rename the columns. You can do this by double-clicking on the column headers and typing in the new names.

6. Now, all you have to do is click "Close & Load" to get your transformed data back into Excel.

Thanks

[ExcelUser]

Thank you for reaching out! I am not able to replicate your step starting with #4.

"Now, you'll notice that the "Attribute" column has both the diagnosis and its influence combined. "

The "Attribute" column does not contain both the diagonis and its influence combined. It now contains only the original columns headings.

I am trying to transform the data so that each row will have 1 Diagnosis and its corresponding

Influence. The steps I took starting with #4 leave the Diagnosis and its Influence in separate rows.