Forum Discussion
Generate incremental number in groups
... 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"
... 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"
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
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:
- Band Column
- SortGroup Column
- second Band Column
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.
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
