Forum Discussion

hkusulja's avatar
hkusulja
Frequent Visitor
4 years ago

How to calculate new column, filter table, sort and select first

Hello, I am using Power Query and got the response. I need to create additional columns, so I can later create hierarchy.   My current table looks like this:   No Name 10000 Assets 1...
Need Help
hkusulja's avatar
hkusulja
Frequent Visitor
4 years ago

Hello,

my table is much bigger, with lot of rows, i do not need to set static value like "assets" or "liabilites", instead it needs to be calculated, based on required formula that i provided. So it is not simple IF statement in M..

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

I need some clarification.

1. On what basis L1 is captured?  

2. On what basis L2 is captured?

Are these based on No column? If yes, what is the logic?

 

  • hkusulja's avatar
    hkusulja
    Frequent Visitor
    4 years ago

    Hello logic for new / calculated formula is as follows:

     

    Level1Category = select text.start (1) , filter whole same table where starting No is with that same text, sort by asc, select Name from first row

     

    Level2Category = select text.start (2) , filter whole table, starting No with that text, sort by asc , select Name from first row

     

    So selecting first (one or two characters), and find first (sorted-asc) Name which has the same first characters.

     

    something like (not correct syntaxt, just indication)

     

    Level1Category = (select value 'name', select first row (sorttable ( tablefilter (each row No starts with = firstcharacter of No)))