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

[hkusulja]

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
11000	Bank accounts
11001	Bank 1
11002	Bank 2
1200	Equipment
1201	Eq type 1
2000	Liabilites
2100	Accounts
2101	Accounts payable domestic

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 row

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

Example output shold be:

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

[Vijay_A_Verma]

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"

 👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data

[hkusulja]

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..

[Luis98]

Good morning @hkusulja!

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:

100	Assets
110	Bank Accounts
120	Equipment
200	Liabilities
210	Accounts

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.

I hope this help you.

[Vijay_A_Verma]

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]

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)))