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"
Yes, it is absolutely possible with PQWRY
let
Source = Web.BrowserContents("https://stackoverflow.com/questions/68577466/when-a-condition-is-met-show-current-row-value-on-each-line-below-it-where-the"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR > :nth-child(1)"}, {"Column2", "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR > :nth-child(2)"}}, [RowSelector="DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR"]),
#"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Description", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Added Index", each Text.StartsWith([Description], "Category")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Index", Order.Ascending}}),
#"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "catIndex", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(#"Added Index", {"Description", "Index"}, #"Added Index1", {"Description", "Index"}, "Added Index1", JoinKind.LeftOuter),
#"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"catIndex"}, {"catIndex"}),
#"Filled Down" = Table.FillDown(#"Expanded Added Index1",{"catIndex"}),
#"Grouped Rows" = Table.Group(#"Filled Down", {"catIndex"}, {{"ad", each _, type table [ID=nullable number, Description=nullable text, catIndex=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let x = [ad],
#"Sorted Rows1" = Table.Sort(x,{{"Description", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows1", "Custom", each #"Sorted Rows1"[Description]{0}),
#"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Description] <> #"Sorted Rows1"[Description]{0})),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"ID", "Description", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Custom", "Category"}})
in
#"Renamed Columns"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"catIndex", "ad"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"ID", "Description", "Category"}, {"ID", "Description", "Category"})
in
#"Expanded Custom"
