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