Forum Discussion
Solved
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...
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"
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!