Use some cell values as categories for other values from the same column

Hi Danielnir ,

I think you'll have to use a conditional column in order to pick out your header values for later use.

I'd create a custom column first, like this:

if List.Contains({"Sales", "Purchases", "Direct Expenses", "Something Else Etc"}, [Title])
then [Title] else null

From here, you can just select your new custom column and go to the Transform tab > Fill (dropdown) > Down.

You now have the headers alongside the relevant rows which you can further filter/transform for more detailed needs.

Example output:

Example code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MSS1WitWJVtJFAmAB5/y8kqLE5BIFhBrfzOJkBc+85PzcVDA/KKbUwMDIzAXMARMBpUXJGYnFuMz0TSxJLcpMzCmG2pBbkJ+XmldSjDDAJbMoFWina0VBah5OY3wSk/JLi2Bm5GYWF2fm54G5wSX5RYnpQNfFAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Title = _t]),
    addConditionalCol = Table.AddColumn(Source, "header", each if List.Contains({"Sales", "Purchases", "Direct Expenses", "Something Else Etc"}, [Title])
then [Title] else null),
    fillDown = Table.FillDown(addConditionalCol,{"header"})
in
    fillDown

Pete