Hello,
thanks to everyone who participated! Everyone who solved this advanced workout is a winner!
As you can see above there are many possibilities with varying degrees of difficulty to solve the workout. I’ll present two options which rely heavily on Table.AddColumns. With the first one I present a neat technique which @valeriabreveglieri used to indentify data types Rick de Groot and with the second I present a useful pattern which allows you to shift a column at ease Imke Feldmann.
Both options start off with two Added Columns: Go to the Add Column tab and chose Custom Column. First time we want to identfy the dates from Column1 and if there is a date we take it over and if not we take null. We can use this for the task:
try Date.From([Column1]) otherwise null
It makes sense to add also type date.
Second time we want to take over the Category from Column2, so if there is “Category” written in Column1 we take Column2 and if not we take null:
if [Column1]=“Category” then [Column2] else null
Next we fill down the values on both Added Customs. Mark both columns and then right click and select Fill > Down
Then we need to select the relevant rows. We don’t want the rows with Emplyoyee or Category and we don’t want empty rows:
each [Column1] <> “Employee” and [Column1] <> “Category” and [Column2] <> null
From here you have several options to deal with the data. Grouping would be a common one, but I think Unpivoting probably offers the easiest way forward. Mark the last 2 columns before going to the Transform tab to select Unpivot Columns > Unpivot Other Columns.
Change “Value” in the formula bar to “Employee”.
Then let’s mark the Attribute column and press Delete to remove it.
So far we had 6 relatively easy steps - only the first one is a bit more avanced.
let
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
#"Added Date" = Table.AddColumn(Source, "Date", each try Date.From([Column1]) otherwise null, type date),
#"Added Category" = Table.AddColumn(#"Added Date", "Category", each if [Column1]="Category" then [Column2] else null),
#"Filled Down" = Table.FillDown(#"Added Category",{"Date", "Category"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down", each [Column1] <> "Employee" and [Column1] <> "Category" and [Column2] <> null),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Date", "Category"}, "Attribute", "Employee"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
.....
First Option:
We use another Added Column for the Points. So go to the Add Column tab and chose Custom Column. This time we want to take over all number from the Employee column and if there is a name we take null. If you think about it we could use a variant from the technique in step one for it (try it yourself), but this super short code will do the trick - you need to learn it:
if [Employee] is text then null else [Employee]
Then fill up the values on the new Added Custom. Mark the column, right click and then select Fill > Up
Finally we need to select all rows where Employee is not equal to Points. Select any of the columns and select any of the values and then bring this to the formula bar: each [Employee] <> [Points]
Done! What I like about it is that it contains incredible useful concepts you can apply over and over again and at the same time is should be still understandable even for not so advanced users.
#"Added Custom" = Table.AddColumn(#"Removed Columns", "Points", each if [Employee] is text then null else [Employee]),
#"Filled Up" = Table.FillUp(#"Added Custom",{"Points"}),
#"Filtered Rows1" = Table.SelectRows(#"Filled Up", each [Employee] <> [Points])
in
#"Filtered Rows1"
Second option:
You need to learn this Shift pattern from Imke Feldmann, so let’s use this task as an excuse. I marked the prevStep the to be shifted ColumnName and the resulting shifted column in italics:
Table.FromColumns(
Table.ToColumns(#“Removed Columns”) & { List.Skip(#“Removed Columns”[Employee]) },
Table.ColumnNames(#“Removed Columns”) & {“Points”} )
Then you just need to get rid of every second row (Home tab > Remove Rows > Remove Alternate Rows) or you selct rows with the thechnique seen above (each [Employee] is text) or …
[100% clicking fun: Alternatively right click on the Points column and Change Type to Whole Number. Then click on the Home tab > Remove Rows > Remove Errors.]
Done! The shift pattern is a bit more complex, but it is incredible useful, so worth learning it.
Shift = Table.FromColumns(
Table.ToColumns(#"Removed Columns") & { List.Skip(#"Removed Columns"[Employee]) },
Table.ColumnNames(#"Removed Columns") & {"Points"} ),
#"Filtered Rows1" = Table.SelectRows(Shift, each ([Employee] is text))
in
#"Filtered Rows1"
Try both options, but make sure to compare them also to the other solutions, following completely different approaches! => Hope you learn something!
Big thanks to everyone who has blured or hidden the details! I have taken this now out so that it’s easier to compare solutions.
