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
Most Valuable Professional
4 years ago

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