I used Power query to get data for each person (a total of 3 families and a total of 6 kids). On the columns M (Monday), T (Tuesday), W (Wednesday), Th (Thursday), and F (Friday), there may or may not have data (D.1, D.2, D.3, D.4, and D.5). For example, The one with Last Name, Krughlikov, do not have D.5 so it has only 4 rows while others have 5 rows.
Under Power Query, how can I combine (any combination of D.1, D.2, D.3, D.4, and D.5) them into only one row, hence there is only one row for each child? (please disregard the first attachment _Question-1.xlsx)
Hi Jim,
I'd remove the D. from each value using Replace. Then format M through F columns as a number data type. This way you can use the Group By tools to summarise the data. You can always add the D. back to the values if they're really necessary.
See example attached.
Mynda
Hi Mynda,
I used group by and aggregate Max(M), Tuesday, Wednesday, Thursday and Friday
Seems like it works too
