Forum Discussion

Danielnir's avatar
Danielnir
Icon for Helper II rankHelper II
3 years ago

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

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?

Need Help

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Danielnir
      Icon for Helper II rankHelper II
      3 years ago

      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's avatar
        BA_Pete
        Icon for Super User rankSuper User
        3 years ago

         

        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