Numeric Text Ranking

imalick1  offcourse, M in PBI can handle that

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    Bins_Sheet = Source{[Item="Bins",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Bins_Sheet, [PromoteAllScalars=true]),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Promoted Headers", [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers1",{"Bins"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Bins] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "CAT", each "Apartment"),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom", "Rank", 1, 1),
    Custom1 = Table.SelectColumns(#"Promoted Headers1",{"Bins_3"}),
    #"Filtered Rows1" = Table.SelectRows(Custom1, each ([Bins_3] <> null)),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows1",{{"Bins_3", "Bins"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "CAT", each "Land"),
    #"Added Index1" = Table.AddIndexColumn(#"Added Custom1", "Rank", 1, 1),
    Custom2 = Table.SelectColumns(#"Promoted Headers1",{"Bins_5"}),
    #"Filtered Rows2" = Table.SelectRows(Custom2, each ([Bins_5] <> null and [Bins_5] <> "#")),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows2",{{"Bins_5", "Bins"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns1", "CAT", each "Price"),
    #"Added Index2" = Table.AddIndexColumn(#"Added Custom2", "Rank", 1, 1),
    Custom3 = #"Added Index"&#"Added Index1"&#"Added Index2",
    #"Inserted Merged Column" = Table.AddColumn(Custom3, "New_Bin_Name", each Text.Combine({Text.From([Rank], "en-US"), [CAT], [Bins]}, "-"), type text)
in
    #"Inserted Merged Column"

Apartment_Bin

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    #"Transaction Data_Sheet" = Source{[Item="Transaction Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Transaction Data_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Transaction", type text}, {"Asset", type text}, {"Date", type date}, {"Transaction Number", Int64.Type}, {"Value", Int64.Type}, {"Area", type number}, {"Price/SQM", type number}, {"Area Bin", type text}, {"Price Bins", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type",{"Area Bin"},Apartment_Bin,{"Bins"},"Apartment_Bin",JoinKind.LeftOuter),
    #"Expanded Apartment_Bin" = Table.ExpandTableColumn(#"Merged Queries", "Apartment_Bin", {"CAT", "New_Bin_Name", "Rank"}, {"CAT", "New_Bin_Name", "Rank"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Apartment_Bin",{{"CAT", Order.Ascending}, {"Rank", Order.Ascending}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows",{"Transaction", "Asset", "Date", "Transaction Number", "Value", "Area", "Price/SQM", "Area Bin", "CAT", "Rank", "Price Bins"})
in
    #"Reordered Columns"

Transaction Data

If you don't like M, the DAX solution would be following

Desire_Bin_Name = CONCATENATE('Apartment_Bin'[Rank],CONCATENATE("-",CONCATENATE('Apartment_Bin'[CAT],CONCATENATE("-",'Apartment_Bin'[Bins]))))