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

[Danielnir]

Hi,

I have Profit and Loss data coming from the server. I need to use some of the values in one column as a category for other values from the same column. Is it possible to do such thing without creating a conditional column? Data tend to be fluid so I would like to avoid manually changing it each time.

The column looks like this:

Hope it makes sense. 

May I ask for your assistance?

[BA_Pete]

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

[Danielnir]

Hi @BA_Pete,

thanks for your quick reply. Unfortunately, the solution didn't work for me as there are many unique values and nearly none of them consist of words that would contain a category name in them.

I think that I will have to force myself to write a long conditional column and also use excel to first categorise everything by hand. Ech..

[BA_Pete]

Hold your horses my man! Where there's a will, there's a way.

Was my broad understanding of your requirement correct - do you essentially want that output, but without hard-coding the header names?

If so, then do the header names that you want carried down ALWAYS have a '----------' row directly underneath them? We could use this characteristic to dynamically identify them.

Pete

[Danielnir]

Yeah, that data isn't very clean and no, not all categories have "-------" beneath them. I don't want to waste your time I will go for creating a massive conditional column.

I'm going to use excel for this as it will help me get the m language formula together. I will achieve that by writing parts of code in each column and then using "concat" function to bring it all together. No running away from hard categorising the data first I think 😄

Just thought that there is a button or other super simple solution that I wasn't aware of.

Thanks for your willingness to help,

Daniel

[BA_Pete]

No problem, best of luck 👍

Pete