March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi
i have a sample table like
price pricebracket
1 Under 1000
2 Under 1000
3 Under 1000
4 Under 1000
.
.
1001 [01 -2 K]
1002 [01 -2 K]
.
.
How can i generate an extra column using M or DAX to make a SortColumn like ....
price pricebracket SortOrder
1 Under 1000 1
2 Under 1000 1
3 Under 1000 1
4 Under 1000 1
.
.
1001 [01 -2 K] 2
1002 [01 -2 K] 2
.
.
i need it to be Automaticly generated becasue the $ range can be anything imported from a data source like SQL
thanks
Solved! Go to Solution.
... editor eating my code again ...
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"pricebracket"}, {{"Min", each List.Min([price]), type number}, {"AllRows", each _, type table}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Min", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"pricebracket", "Min"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"price", "pricebracket"}, {"price", "pricebracket"})
in
#"Expanded AllRows"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Only mildy amusing - all previews look fine and once posted randomly cut.
Hopefully this link the the xlsx containing the M-code will survive: File
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Nik,
guessing that the steps speak for themselves:
let Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"pricebracket"}, {{"Min", each List.Min([price]), type number}, {"AllRows", each _, type table}}), #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Min", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"pricebracket", "Min"}), #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"price", "pricebracket"}, {"price", "pricebracket"}) in #"Expanded AllRows"
But otherwise just let me know where you need an explanation.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
... editor eating my code again ...
let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"pricebracket"}, {{"Min", each List.Min([price]), type number}, {"AllRows", each _, type table}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Min", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1),
#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"pricebracket", "Min"}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Columns", "AllRows", {"price", "pricebracket"}, {"price", "pricebracket"})
in
#"Expanded AllRows"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Only mildy amusing - all previews look fine and once posted randomly cut.
Hopefully this link the the xlsx containing the M-code will survive: File
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Thank you for the answer, I just want to say that the if i have to add another Band Column (this happens alot in insurance company) then i will have to add another SortGroup Column for the second Band Column and etc...
I don't think that is a good approch and the BI team should fix the issues that sorting a column must not depend on the preivious column, now i can add as much as new Banding field without change the sort or having an extra GroupSort field for every new Band Field. for me this is a limitation and something that i don't like to tell my client
I hope that it was clear, anyways thank you again.
Hi Nik,
I might be able to understand sth if I had an idea about what the new columns you introduced in this new request mean:
The only columns I'm currently aware of are: price and pricebracket.
As you see in my Excel-example I've added some more pricebrackets, you could add some as well (as rows) - so really no idea what the current problem is.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi Imfef
Ok i'm going to give you an example in insurance,
as a designer i make a DimAge as mentioned, as you can see i have 2 Age bands that are mainly for Life insurance, in Life insurance they do not study any ages under 18.
Now imagin i have to design for "Car insurance", in the car insurance i will need DimAge but within a different Age band, Analyst do care/study about ages under 18, they even have age 0 (Zero) that they study. anyways as a designer i will be using the same DimAge for Life insurance and Car Insurance, the thing is that i will add to the DimAge a new field AgeBand3 for the Car insurace and etc... and on top of all of the above i may have a Analyst that might have a different point of view in the AgeBanding (AgeBanding4). my point is that within 10-20 min i can add this to my design,
I have been doing this in SSAS and it's dirt simple and easy to add maintain etc...
my point is that within Power BI i can not simple add a Field to my DimAge as simple as i was doing in SSAS i will have to add 2 columns for a new Band and etc.... plus i will have to do more test to make sure i can switch form one band to another and anve no truble from a end user point of view
i hope it was clear,
let
// Make a Age Range, Max number is 127 else if needed more you must change data type from Int8 to Int16
Source = {-1.. 127
Sorry the code got trimmed off
let
// Make a Age Range, Max number is 127 else if needed more you must change data type from Int8 to Int16
Source = {-1.. 127},
// Convert List to table
ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// Rename field to [Age]
RenamedColumns = Table.RenameColumns(ConvertedToTable,{{"Column1", "Age"}}),
// Chnage the data type of the field [Age] to Int8 (-127 ... +127)
ChangeDataType= Table.TransformColumnTypes(RenamedColumns ,{{"Age", Int8.Type}}),
// Add AgeBand1
AddedAgeBand1Column = Table.AddColumn(ChangeDataType, "AgeBand1", each
if -1 = [Age] then "Unknown"
else if 0 <= [Age] and [Age] < 18 then "Under 18"
else if 18 <= [Age] and [Age] <= 24 then "[18-24]"
else if 25 <= [Age] and [Age] <= 34 then "[25-34]"
else if 35 <= [Age] and [Age] <= 44 then "[35-44]"
else if 45 <= [Age] and [Age] <= 54 then "[45-54]"
else if 55 <= [Age] and [Age] <= 64 then "[55-64]"
else if 65 <= [Age] and [Age] <= 74 then "[65-74]"
else if 75 <= [Age] and [Age] <= 84 then "[75-84]"
else if 85 <= [Age] and [Age] <= 94 then "[85-94]"
else if 95 <= [Age] and [Age] <= 104 then "[95-104]"
else if 105 <= [Age] and [Age] <= 114 then "[105-114]"
else if 115 <= [Age] and [Age] <= 124 then "[115-124]"
else "[125 +"),
// Add AgeBand2
AddedAgeBand2Column = Table.AddColumn(AddedAgeBand1Column, "AgeBand2", each
if -1 = [Age] then "Unknown"
else if 0 <= [Age] and [Age] < 20 then "Under 20"
else if 20 <= [Age] and [Age] <= 29 then "[20-29]"
else if 30 <= [Age] and [Age] <= 39 then "[30-39]"
else if 40 <= [Age] and [Age] <= 49 then "[40-49]"
else if 50 <= [Age] and [Age] <= 59 then "[50-59]"
else if 60 <= [Age] and [Age] <= 69 then "[60-69]"
else if 70 <= [Age] and [Age] <= 79 then "[70-79]"
else if 80 <= [Age] and [Age] <= 89 then "[80-89]"
else if 90 <= [Age] and [Age] <= 99 then "[90-99]"
else if 100 <= [Age] and [Age] <= 109 then "[100-109]"
else if 110 <= [Age] and [Age] <= 119 then "[110-119]"
else "[120 +")
in
AddedAgeBand2Column
OK i just tested 2 age band and i need 2 extra [Group Sort Order] field, one for each AgeBand
so that is toooooo much work, i am going to assume that in the future they will fix this issues and come up with a more simple solution.
Thanks Nik,
starting to get the picture, but not fully there yet.
But you might want to look into these features:
List.Generate lets you generate a list according to the different rules per AgeBand. This article explains how you apply it: http://blog.crossjoin.co.uk/2014/06/25/using-list-generate-to-make-multiple-replacements-of-words-in...
Expression.Evaluate: Might be an alternative if List.Generate doesn't deliver or to feed in the rules from a table. This is the only article I'm aware of: https://bondarenkoivan.wordpress.com/2016/01/25/rename-columns-of-nested-tables-in-power-query/
You don't have to create them as additional columns manually. Just create them in one column with an added column containing their name and pivot on it at the end.
Not sure if this will work at the end, but definitely some more options to look into.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
90 | |
86 | |
83 | |
72 | |
49 |
User | Count |
---|---|
167 | |
149 | |
98 | |
73 | |
57 |