Forum Discussion

imalick1

Frequent Visitor

7 years ago

Solved

Numeric Text Ranking

Hi All, I have numeric text fields as follows:

• 0-49

• 50-99

• 100-149

• 150-199

• 200-259

• 250-299

• 300-349

• 350-499

• 500+

These were created as bins, as I was unable to develop for my large dataset (20,000+ items) into these nice categories. However, the issue is that powerbi does not rank it accordingly. but as follows:

• 100-149

• 150-199

• 200-259

• 250-299

• 300-349

• 350-499

• 50-99

• 500

Appreciate the community's help in resolving these issues. Furthermore, I will be thankful, if I can create fixed dynamically, updating bins based on my range limits. I have tried the group feature, whereby I manually, select the values.

However, this is difficult due to a large number of items.

Thanks.

Need Help

smpa01

7 years ago

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

8 Replies

smpa01
Community Champion
7 years ago
imalick1can you please provide a sample data-set and the output that you desire

Nolock

Resident Rockstar

7 years ago

Hi imalick1,

if I unterstand you well, you want to sort values in your range column by the first part of the range as it were integers and not strings.

Use PowerQuery Editor for duplicating your range column, split the column into 2 by non-digit, change the data type of the new to whole number.
Apply your model.
Sort your range column by the new one, see screenshot.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NczBDcAwCEPRXbimSAbCIbMg9l+jRHVv1pP1qwS6j/RTktDzLQPUqDZsdB/35B53eowH/5E3+BexpPsF", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RangeColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RangeColumn", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [RangeColumn]),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Custom.1", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition",{"Custom.2"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "Custom.1", "Custom.1 - Copy"),
    #"Removed Columns1" = Table.RemoveColumns(#"Duplicated Column",{"Custom.1 - Copy", "Custom.1"}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Removed Columns1", "RangeColumn", "RangeColumn - Copy"),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Duplicated Column1", "RangeColumn - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"RangeColumn - Copy.1", "RangeColumn - Copy.2"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Split Column by Character Transition1",{"RangeColumn - Copy.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"RangeColumn - Copy.1", "RangeColumnToSort"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"RangeColumnToSort", Int64.Type}})
in
    #"Changed Type1"

And to see all the steps: PowerBI file with code

imalick1

Frequent Visitor

7 years ago

Nolock this seems a very complex solution.

smpa01 here is a sample of the data, https://1drv.ms/x/s!AsiOVEWuhqNbjOFJxqeTvbYVs2w2ig

smpa01

Community Champion

7 years ago

You mentioned - "However, the issue is that powerbi does not rank it accordingly" - where do you want to see this rank ? In the query window (M) or in the visualisations (DAX) that you form with the data?

I assumed that you want to see the ranking in the query window therefore I coded it as following for the ease of your understanding.

Also, in your original data there are two bin columns - "Area Bin" and "Price Bins". Not sure, Ranking depends on which bin. I assumed that you want the ranking to be generated based on the Area bin.

The code is following

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"
in
    Custom3

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", "Rank"}, {"CAT", "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

Forum Discussion

Numeric Text Ranking

8 Replies

Related content

Duplicate ranking

ranking on text column

Ranking KPI card

Rank with conditions

Dynamic Ranking