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
Vijay_A_Verma's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost 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)))