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

Question

Hello,I am using Power Query and got the response.I need to create additional columns, so I can later create hierarchy.&nbsp;My current table looks like this:&nbsp;NoName10000Assets11000Bank accounts11001Bank 111002Bank 21200Equipment1201Eq type 12000Liabilites2100Accounts2101Accounts payable domestic&nbsp;&nbsp;So i need to create additional columns, please help with M languge / Power Query formula:Level1Category = select text.start (1) , filter whole table, starting No with that text, sort by asc , select Name from first rowLevel2Category = select text.start (2) , filter whole table, starting No with that text, sort by asc , select Name from first row&nbsp;Example output shold be:&nbsp;NoNameL1L210000AssetsAssetsAssets11000Bank accountsAssetsBank Accounts11001Bank 1AssetsBank Accounts11002Bank 2AssetsBank accounts1200EquipmentAssetsEquipment1201Eq type 1AssetsEquipment2000LiabilitesLiabilitesLiabilites2100AccountsLiabilitesAccounts2101Accounts payable domesticLiabilitesAccounts

vijay_a_verma · Answer

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc4xDoMwDAXQq0SZGWrfoJW69QYogwkeLCCkihm4fSPcoHh8X1/f4+jhUc8P/lkKa/FhqARGL0qLoxj3I3UJtARuwkZohFf9/T0kb5y0IVzo9Mz8L6MNfYQmWUXZVhDso365GnTmMp00rezmfeOiEn0IPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [No = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No", Int64.Type}, {"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "L1", each if [Name]="Assets" or [Name]="Liabilites" then [Name] else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"L1"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "L2", each if [Name]="Assets" or [Name]="Liabilites" then [Name] 
else if List.Contains({"Bank Accounts", "Equipment","Accounts"},[Name],Comparer.OrdinalIgnoreCase) then [Name] else null, type text),
    #"Filled Down1" = Table.FillDown(#"Added Custom1",{"L2"})
in
    #"Filled Down1"
&nbsp;👍&nbsp;It's been a pleasure to help you |&nbsp;Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly&nbsp;--&nbsp;How to provide sample data

hkusulja · Answer

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 · Answer

I need some clarification.
1. On what basis L1 is captured?&nbsp;&nbsp;
2. On what basis L2 is captured?
Are these based on No column? If yes, what is the logic?
&nbsp;

anonymous · Answer

Good morning&nbsp;hkusulja!&nbsp;I think it is not so much a problem of M language, but of specifying in an auxiliary table a mapping of your accounts.For example for this example table that you put would be the following:&nbsp;100Assets110Bank Accounts120Equipment200Liabilities210Accounts&nbsp;So if you extract the first digit on the left you could do an IF condition that tells you if it is Assets or Liabilities and if you extract the first 3 digits you could get your L2.&nbsp;I hope this help you.

hkusulja · Answer

Hello logic for new / calculated formula is as follows:&nbsp;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&nbsp;Level2Category = select text.start (2) , filter whole table, starting No with that text, sort by asc , select Name from first row&nbsp;So selecting first (one or two characters), and find first (sorted-asc) Name which has the same first characters.&nbsp;something like (not correct syntaxt, just indication)&nbsp;Level1Category = (select value 'name', select first row (sorttable ( tablefilter (each row No starts with = firstcharacter of No)))

Forum Discussion

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

5 Replies

Related content

Calculating with Filters

Adding filters to a calculated column

TMDL - calculated column

Slicer for filtering several columns

Calculate Filters

No	Name
10000	Assets
11000	Bank accounts
11001	Bank 1
11002	Bank 2
1200	Equipment
1201	Eq type 1
2000	Liabilites
2100	Accounts
2101	Accounts payable domestic