is it possible via either Power Query or Power Pivot to get from following table
| ID | Description |
|---|---|
| Category1 | |
| 1 | Task1 |
| 2 | Task2 |
| Category2 | |
| 3 | Task3 |
| 4 | Task4 |
a table, where the category is in a separate column for each row under it?
| ID | Description | Category |
|---|---|---|
| 1 | Task1 | Category1 |
| 2 | Task2 | Category1 |
| 3 | Task3 | Category2 |
| 4 | Task4 | Category2 |
I have tried to do it with some indexed columns or FIRSTNONBLANK but I have failed miserably.
An alternate method
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Category", each if [ID]=null then [Description] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Description.1", each if [ID]<>null then [Description] else null),
#"Filled Down" = Table.FillDown(#"Added Custom1",{"Category"}),
#"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Description"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Description.1] <> null))
in #"Filtered Rows"