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"
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.. 127Sorry 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
AddedAgeBand2ColumnOK 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.