Using Power Query to Group & Transpose Data

Question

Need help converting a database that looks like this:

Into this:

(subsequent columns to the right are not in view)

*Note: data presented was made up for illustration purposes

I began using Excel's Power Query to group by ID and I get a column with "tables" for each ID, but when I expand the columns I go back to square 0. I'm trying to see how I can transpose the data so that I can get new columns in the same format as the other ones (date 2, value2, procedure 2, date 3, value 3, procedure 3, etc) with values from each row of each participant. Essentially, I want one row per ID with all of the participant's data in subsequent columns. I've looked at different web posts and tutorials but have not been able to figure out how to do this. Any ideas or suggestions are greatly appreciated!

May I ask why? The current format is much easier to work with than what you are suggesting. — Alexis Olson
I agree but my PI/mentor believes that for data analysis purposes we should have the data organized one row per ID. Also, we will be extracting this medical data on a quarterly basis so she believes that adding additional columns as we receive new data would be easier, though I disagree. Please let me know if you have any other ideas or suggestions. Would we be able to conduct data analysis in the current format? Thanks! — Patricia P
The long format you currently have it in is much more conducive to data analysis, IMO. Wide data has its purposes but I'd recommend reading about Tidy Data before settling on it. — Alexis Olson
Thanks so much, Alexis! I will take a look at that reading and discuss with my mentor. — Patricia P

Chris Chris · Accepted Answer · 2020-05-21T09:10:19

you need an additional Group-Counter/Index and then a combination of unpivot an pivot columns. Here is a code. I named my source-table "tbl_Source" in the excel sheet.

let
    Source = tbl_Source,
    Group_Rows = Table.Group(Source, {"ID"}, {{"KPI", each _, type table [ID=number, Attribut=text, Wert=anynonnull]}}),
    Add_Custom_Column_Index = Table.AddColumn(Group_Rows, "Index", each Table.AddIndexColumn([KPI], "Index", 1, 1)),
    Expand_Index = Table.ExpandTableColumn(Add_Custom_Column_Index, "Index", {"Date", "Lab_Type", "LabName", "Value", "Unit", "ReferenceValues", "Index" }, {"Date", "Lab_Type", "LabName", "Value", "Unit", "ReferenceValues", "Index"}),
    Delete_Column = Table.RemoveColumns(Expand_Index,{"KPI"}),
    Unpivot_Other_Columns = Table.UnpivotOtherColumns(Delete_Column, {"ID", "Index"}, "Attribut", "Wert"),
    Combine_Columns = Table.CombineColumns(Table.TransformColumnTypes(Unpivot_Other_Columns, {{"Index", type text}}, "de-DE"),{"Attribut", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"ColumnHeader"),
    Pivot_Columns = Table.Pivot(Combine_Columns, List.Distinct(Combine_Columns[ColumnHeader]), "ColumnHeader", "Wert"),
    /*Change Type of all Date Columns */
    ChangeType_Date = Table.TransformColumnTypes(Pivot_Columns, 
                             List.Transform(List.Select(Table.ColumnNames(Pivot_Columns), 
                                         each Text.StartsWith(_, "Date")
                              ),
                            each {_, type date}))
in
    ChangeType_Date

Best regards Chris

The next question of your PI/mentor should be "How do I unpivot this data ;-) "

Using Power Query to Group & Transpose Data

1 Answers