Add custom column that increments based on condition

[cgkas]

Hello to all,

I´m very new to Power Query, Power BI and M language.

May someone please give me some help. I´d like to know how to add a custom column that increments each time that column MONTH = "February" or "September"

I´m not sure if it is possible to add a custom fuction something like this:

i=0
if [MONTH] = "February" or [MONTH] = "September" 
   then [CUSTOM] = i + 1 
else
   [CUSTOM] = null

To get an output like this in column CUSTOM

MONTH	CUSTOM
March
February	1
June
June
July
August
September	2
January
September	3
February	4
November
November
December
September	5
December
January
February	6
March

Thanks in advance for any help.

[Zubair_Muhammad · Accepted solution]

@cgkas 

Please see if this is useful
File attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUvBNLErOUFDSUVJQitUB8t1Sk4pKE4sqQUKGECGv0rxUJBUY3JxKJK5jaXppcQmSQHBqQUlqblJqEUjMCKonMQ9mBzZVxphOMYEI+eWXwVUp4BRySU1GF0Ix3xSnMkyHITvCDCIEDzKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MONTH = _t, CUSTOM = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"CUSTOM"}),
    AddedIndex = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(AddedIndex, each ([MONTH] = " February " or [MONTH] = " September ")),
    AddedIndex1 = Table.AddIndexColumn(#"Filtered Rows", "Custom", 1, 1),
    #"Merged Queries" = Table.NestedJoin(AddedIndex,{"MONTH", "Index"},AddedIndex1,{"MONTH", "Index"},"AddedIndex1",JoinKind.LeftOuter),
    #"Expanded AddedIndex1" = Table.ExpandTableColumn(#"Merged Queries", "AddedIndex1", {"Custom"}, {"Custom"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AddedIndex1",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

[Zubair_Muhammad · Accepted solution]

@cgkas 

Please see if this is useful
File attached as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUvBNLErOUFDSUVJQitUB8t1Sk4pKE4sqQUKGECGv0rxUJBUY3JxKJK5jaXppcQmSQHBqQUlqblJqEUjMCKonMQ9mBzZVxphOMYEI+eWXwVUp4BRySU1GF0Ix3xSnMkyHITvCDCIEDzKl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MONTH = _t, CUSTOM = _t]),
    #"Removed Columns" = Table.RemoveColumns(Source,{"CUSTOM"}),
    AddedIndex = Table.AddIndexColumn(#"Removed Columns", "Index", 1, 1),
    #"Filtered Rows" = Table.SelectRows(AddedIndex, each ([MONTH] = " February " or [MONTH] = " September ")),
    AddedIndex1 = Table.AddIndexColumn(#"Filtered Rows", "Custom", 1, 1),
    #"Merged Queries" = Table.NestedJoin(AddedIndex,{"MONTH", "Index"},AddedIndex1,{"MONTH", "Index"},"AddedIndex1",JoinKind.LeftOuter),
    #"Expanded AddedIndex1" = Table.ExpandTableColumn(#"Merged Queries", "AddedIndex1", {"Custom"}, {"Custom"}),
    #"Sorted Rows" = Table.Sort(#"Expanded AddedIndex1",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

[cgkas]

Hello Zubair,

Thanks for answer. It seems to work pretty fine.

Even it works and I could accept it as answer I´d like to know how to do it in form like for loop.

something like this example I took from here

fnIterateNestedLoop = (RowCount, ColumnCount, fnTransformByIndices) =>
 List.Generate(
  ()=>[i=0, j=0],
  each [i] < RowCount,
  each
   if [j] < ColumnCount - 1 then
    [i=[i], j=[j]+1]
   else
    [i=[i]+1, j=0],
  each fnTransformByIndices([i], [j])
 ),

Best regards

[Zubair_Muhammad · Accepted solution]

Hi @cgkas 

I dont think List.Generate would be efficient in this case but here are 2 custom column formulas(Both use Index Column as support). The second one uses List.Generate

Please see table2 in attached file's Query Editor

=if [MONTH] ="February" or [MONTH] = "September" then let myindex=[Index] in
                Table.RowCount
                (Table.SelectRows(AddedIndex,each [Index] <= myindex and ([MONTH] ="February" or [MONTH] = "September")))
else
null

=let myindex=[Index] in if [MONTH] ="February" or [MONTH] = "September" then List.Count(List.Select(List.Generate(()=>[x=0,y=null], each [x]<myindex ,each [x=[x]+1,y=AddedIndex[MONTH]{x}], each [y]),each List.Contains({"February","September"},_))) else null

[cgkas]

Hi Zubair,

Excellent. Thanks so much for the examples shared in both custom formulas. Then  I think first custom formula would be fast that List.Generate() for what you says. But between your first solution and your solution with first custom formula, which would be faster?

Thanks so much fot the help

[Zubair_Muhammad]

@cgkas 

Custom Column is simpler and should be faster