cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HTK421
Frequent Visitor

Create a Column that increments when another column changes

How do I create a colum (B) that starts at "1" and increments when column A changes? 🙂

 

HTK421_0-1613844511757.png

 

1 ACCEPTED SOLUTION
ImkeF
Super User II
Super User II

Hi @HTK421 ,

if you group on ColumnA/Week Number with GroupKind.Local and select "All Rows" in the aggregation, you'll get a table where you simply add an index column to. Then expand the grouped columns again:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVUitVBUEZgyhCDNIKRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week number1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week number1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Week number1"}, {{"All", each _, type table [Week number1=nullable number]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Group", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Week number1"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Week number1"}, {"Week number"})
in
    #"Expanded All"




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

4 REPLIES 4
Rocco_sprmnt21
Super User II
Super User II

here  you can find some solutions(some of which are very similar to the one proposed here ) to identical problem

Thank you I appreciate the links.

 

ImkeF
Super User II
Super User II

Hi @HTK421 ,

if you group on ColumnA/Week Number with GroupKind.Local and select "All Rows" in the aggregation, you'll get a table where you simply add an index column to. Then expand the grouped columns again:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjVUitVBUEZgyhCDNIKRsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Week number1" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week number1", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Week number1"}, {{"All", each _, type table [Week number1=nullable number]}}, GroupKind.Local),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Group", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Week number1"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Week number1"}, {"Week number"})
in
    #"Expanded All"




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

HTK421
Frequent Visitor

Thank you @ImkeF  I appreciate it

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.