M code for counting rows

[Junaid11]

Hello,

I want to have the M code where I want to count consecutive rows of Flag based on sorted index column (Week_Index) and based on the group (Item), however, value "999" should be ignored.

The data looks like below:

Week_IndexItemFlag

1	HSIW	999
2	HSIW	1
3	HSIW	1
4	HSIW	1
5	HSIW	999
6	HSIW	-1
7	HSIW	-1
8	HSIW	999
9	HSIW	999
10	HSIW	999
11	HSIW	999
12	HSIW	0
13	HSIW	999
14	HSIW	999
15	HSIW	999
1	LPAA	999
2	LPAA	999
3	LPAA	999
4	LPAA	999

The outcome I want in M code for a new column of consecutive counter looks like below:

Week_IndexItemFlagConsecutive_Counter

1	HSIW	999	1
2	HSIW	1	2
3	HSIW	1	3
4	HSIW	1	4
5	HSIW	999	5
6	HSIW	-1	1
7	HSIW	-1	2
8	HSIW	999	3
9	HSIW	999	4
10	HSIW	999	5
11	HSIW	999	6
12	HSIW	0	1
13	HSIW	999	2
14	HSIW	999	3
15	HSIW	999	4
1	LPAA	999	1
2	LPAA	999	2
3	LPAA	999	3
4	LPAA	999	4

Is there a way to get this consecutive counter column with M code?

Thanks

[latimeria]

This query code should do the trick

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfII9gxXsLS0VIrViVYyggkYgrnGqFwTVK4pum4zmIAuRIE5Gt8CXYMluoChAYYIhhsN4Y40gPCNMVSYYIhguBVkrE+AoyOK11EEjNEFTFAEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Week_Index = _t, ItemFlag = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week_Index", Int64.Type}, {"ItemFlag", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "ItemFlag", "ItemFlag - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "ItemFlag - Copy", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"ItemFlag - Copy.1", "ItemFlag - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ItemFlag - Copy.1", type text}, {"ItemFlag - Copy.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","999",null,Replacer.ReplaceValue,{"ItemFlag - Copy.2"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"ItemFlag - Copy.2"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"ItemFlag - Copy.2"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"ItemFlag - Copy.1", "ItemFlag - Copy.2"}, {{"Rows", each _, type table [Week_Index=nullable number, ItemFlag=nullable text, #"ItemFlag - Copy.1"=nullable text, #"ItemFlag - Copy.2"=nullable text]}}),
    Count = Table.TransformColumns( #"Grouped Rows", {"Rows", each Table.AddIndexColumn(_, "Index", 1)}),
    #"Removed Columns" = Table.RemoveColumns(Count,{"ItemFlag - Copy.1", "ItemFlag - Copy.2"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Columns", "Rows", {"Week_Index", "ItemFlag", "Index"}, {"Week_Index", "ItemFlag", "Index"})
in
    #"Expanded Rows"

[Junaid11]

Hello @latimeria ,

It is working perfectly fine but my data is lot more than what i have shown here as well as its source is excel. What can be used to show the exact result. I am attaching my data file to you if possible kindly let me know about it.

Thanks

https://docs.google.com/spreadsheets/d/1bi3PFJ9pkKiKRaf3AVkxgJ3Kmn7_ofSw/edit?usp=sharing&ouid=11426...

[latimeria]

Code to read a google sheet. (replace https:... with your url in google doc)

let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/19ojZAO4U7F1_uT0Kw1jp4lxPNuG3JyzCltfY_vCL7l0/edit#gid=0"),
    #"Feuille 1_Table" = Source{[name="Feuille 1",ItemKind="Table"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Feuille 1_Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Week_Index", Int64.Type}, {"Item", type text}, {"Flag", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","999",null,Replacer.ReplaceValue,{"Flag"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Flag"}),
    #"Filled Up" = Table.FillUp(#"Filled Down",{"Flag"}),
    #"Grouped Rows" = Table.Group(#"Filled Up", {"Item", "Flag"}, {{"Rows", each _, type table [Week_Index=nullable number, Item=nullable text, Flag=nullable text]}}),
    Count = Table.TransformColumns( #"Grouped Rows", {"Rows", each Table.AddIndexColumn(_, "Index", 1)}),
    #"Expanded Rows" = Table.ExpandTableColumn(Count, "Rows", {"Week_Index", "Index"}, {"Week_Index", "Index"})
in
    #"Expanded Rows"

I have never tried to read excel file on google doc. You can read excel file on sharepoint ...

[latimeria · Accepted solution]

replace first line with this code

Source = Excel.Workbook(File.Contents("Your file path + name.xlsx"), null, true),

[Junaid11]

Hello @latimeria ,

I meant to ask that code is working perfectly fine. I put the excel source this error came. Is there anything I am doing wrong in it?

[smpa01]

@Junaid11  what is the logic?

[Junaid11]

Hello @smpa01 ,

The logic is it should keep counting the rows until there is no anyother Flag number comes excluding 999 and until new item changes. As you can see it starts with 1 till 5 and as 999 is excluded so it does not restart the counting so when ever a new number comes except 999 it will restart similarly if new item comes it will start recoundting. If a number +1 is coming again and again it would keep counting even 999 comes but as -1 or 0 comes it will restart to count. Similarly if after +1 a -1 comes and counting starts so it will keep counting unless a number like 0 or +1 comes except 999 which does not imapct any of them.