cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Junaid11
Helper III
Helper III

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

1HSIW999
2HSIW1
3HSIW1
4HSIW1
5HSIW999
6HSIW-1
7HSIW-1
8HSIW999
9HSIW999
10HSIW999
11HSIW999
12HSIW0
13HSIW999
14HSIW999
15HSIW999
1LPAA999
2LPAA999
3LPAA999
4LPAA999

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

Week_IndexItemFlagConsecutive_Counter

1HSIW9991
2HSIW12
3HSIW13
4HSIW14
5HSIW9995
6HSIW-11
7HSIW-12
8HSIW9993
9HSIW9994
10HSIW9995
11HSIW9996
12HSIW01
13HSIW9992
14HSIW9993
15HSIW9994
1LPAA9991
2LPAA9992
3LPAA9993
4LPAA9994

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

Thanks

1 ACCEPTED SOLUTION

replace first line with this code

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

View solution in original post

7 REPLIES 7
latimeria
Resolver I
Resolver I

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

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

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

 

replace first line with this code

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

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?

asdasdfff.PNG

dsfrfggg.PNG

smpa01
Super User
Super User

@Junaid11  what is the logic?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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. 

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!