Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SNik
Helper I
Helper I

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

... 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

View solution in original post

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

View solution in original post

9 REPLIES 9
ImkeF
Super User
Super User

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.

 

Sincerely
Nik- Shahriar Nikkhah

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.

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

Sincerely
Nik- Shahriar Nikkhah

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.