Forum Discussion
M code for counting rows
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
replace first line with this code
Source = Excel.Workbook(File.Contents("Your file path + name.xlsx"), null, true),
7 Replies
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.
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"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
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 ...
