Forum Discussion
Count previous blank value in M Query
Hello,
the below query is not working: Column1 has several values and I added an Index column, for each row I'd like to calculate how many blank value are there in previous rows. Since this query is excecuted on several csv files in a folder I'd like to optimize it, I would like that the file is read only one time and buffered before using it several times: one for its normal shaping and one for each row calculating the count of previous blank row values.
Previous: I mean "with lower index values"
I think I made some mistake after "fx" and in the concept of buffering.
Many thanks
let
Source= Csv.Document(Parametro1,[Delimiter="=", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]),
buffer = Table.Buffer(Source),
#"Add index" = Table.AddIndexColumn(buffer, "Index", 0, 1, Int64.Type),
#"Function" = Table.AddColumn(#"Add index", "fx", each (Index)=> Table.RowCount(Table.SelectRows(#"Add index", each [Column1] = "" and [Indice] < Indice)),Int64.Type),
#"Removed column" = Table.RemoveColumns(#"Function",{"index"})
in
#"Removed column"ImkeF I know she's a Guru on M
another way to get the result
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorViVZKBDJSUksSM3MUSioLUhUMsYoaYRU1hoiiC5uAhZPQRE3BojBrk7Faiy5qhKInBaueVLx60jD1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna2 = _t, Colonna1 = _t]), nrow=Table.RowCount(Origine), ncol=Table.ColumnCount(Origine), ir=Table.InsertRows(Origine,nrow,{Record.FromList(List.Repeat({""},ncol),Table.ColumnNames(Origine))}), #"Aggiunta colonna indice" = Table.AddIndexColumn(ir, "Indice", 1, 1, Int64.Type), #"Ordinate righe" = Table.Sort(#"Aggiunta colonna indice",{{"Colonna1", Order.Ascending}}), #"Aggiunta colonna indice1" = Table.AddIndexColumn(#"Ordinate righe", "Indice.1", 0, 1, Int64.Type), #"Ordinate righe1" = Table.Sort(#"Aggiunta colonna indice1",{{"Indice", Order.Ascending}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Ordinate righe1", "idx", each if [Colonna2]="" then [Indice.1] else null), ria = Table.FillUp(#"Aggiunta colonna personalizzata",{"idx"}), trl=Table.RemoveLastN(ria,1), #"Rimosse colonne" = Table.RemoveColumns(trl,{"Indice", "Indice.1"}) in #"Rimosse colonne"
7 Replies
Ciao Rocco,
My M knowledgeis too basic to understand each step and I don't know why It seems on the right way, but it has some mistake if you have several non blank values at the beginning, I tried to add some heading rows at your source like in the image below.
Grazie
with several starting compiled rows
another way to get the result
let Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRUorViVZKBDJSUksSM3MUSioLUhUMsYoaYRU1hoiiC5uAhZPQRE3BojBrk7Faiy5qhKInBaueVLx60jD1xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna2 = _t, Colonna1 = _t]), nrow=Table.RowCount(Origine), ncol=Table.ColumnCount(Origine), ir=Table.InsertRows(Origine,nrow,{Record.FromList(List.Repeat({""},ncol),Table.ColumnNames(Origine))}), #"Aggiunta colonna indice" = Table.AddIndexColumn(ir, "Indice", 1, 1, Int64.Type), #"Ordinate righe" = Table.Sort(#"Aggiunta colonna indice",{{"Colonna1", Order.Ascending}}), #"Aggiunta colonna indice1" = Table.AddIndexColumn(#"Ordinate righe", "Indice.1", 0, 1, Int64.Type), #"Ordinate righe1" = Table.Sort(#"Aggiunta colonna indice1",{{"Indice", Order.Ascending}}), #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Ordinate righe1", "idx", each if [Colonna2]="" then [Indice.1] else null), ria = Table.FillUp(#"Aggiunta colonna personalizzata",{"idx"}), trl=Table.RemoveLastN(ria,1), #"Rimosse colonne" = Table.RemoveColumns(trl,{"Indice", "Indice.1"}) in #"Rimosse colonne"This works and it's very efficient!
