Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
imalick1
Frequent Visitor

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.

1 ACCEPTED SOLUTION

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

Capture.JPG

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

8 REPLIES 8
Nolock
Resident Rockstar
Resident Rockstar

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.

  1. 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.
  2. Apply your model.
  3. 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"

Capture.PNG

And to see all the steps: PowerBI file with code

@Nolock this seems a very complex solution.

 

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

 

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

 

Capture.JPG

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

I want to see the correct order in the actual visualisation table, bar graph etc.  The preference is to show this information visually, as opposed to the query. 

 

For example, if you try to create a bar chart from the data I provided it will just give the areas along the x-axis.  I was trying to create "bins" that consolidates the area and price into user-defined groups.  So as a workaround I did it in excel and used them for reporting. 

 

 

First of all bin values (0-99, 0-50 etc ctc) are text fields.

Hence, they can't be organised the way in the viz I/you would easily classify them. But the following is the fix that can be applied

 

Capture.JPG

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Thanks @smpa01 I think I will use the visual method you showed me.  

 

However, is it possible to create bins in this fashion, using powerbi?

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

Capture.JPG

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
smpa01
Super User
Super User

@imalick1can you please provide a sample data-set and the output that you desire

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.