Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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]))))
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.
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
@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
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
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]))))
@imalick1can you please provide a sample data-set and the output that you desire
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |