cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SNik Regular Visitor
Regular Visitor

Generate incremental number in groups

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

Sincerely
Nik- Shahriar Nikkhah
2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: 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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Generate incremental number in groups

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




9 REPLIES 9
Super User
Super User

Re: Generate incremental number in groups

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: 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"

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Generate incremental number in groups

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




SNik Regular Visitor
Regular Visitor

Re: Generate incremental number in groups

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.

 

Sincerely
Nik- Shahriar Nikkhah
Super User
Super User

Re: Generate incremental number in groups

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




SNik Regular Visitor
Regular Visitor

Re: Generate incremental number in groups

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

Sincerely
Nik- Shahriar Nikkhah
SNik Regular Visitor
Regular Visitor

Re: Generate incremental number in groups

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

Sincerely
Nik- Shahriar Nikkhah
SNik Regular Visitor
Regular Visitor

Re: Generate incremental number in groups

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.

Sincerely
Nik- Shahriar Nikkhah
Super User
Super User

Re: Generate incremental number in groups

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.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 263 members 2,906 guests
Please welcome our newest community members: