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