Forum Discussion

AGo's avatar
AGo
Post Patron
5 years ago
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...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    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"

     

     

     

     

AGo's avatar
AGo
Post Patron
5 years ago

I'm sorry to say that it yet has a mistake when calculating on the first row if it's not blank.

I made this practical case as example:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpJLUnMzFEoqSxIVTBUitXBFDXCKmoMEUUXNgELJ6GJmoJFgYJgOhmrteiiRih6UrDqScWrJw1TTywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Colonna2 = _t, Colonna1 = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"Colonna2"}, {{"all", each _}}, GroupKind.Local, (x,y)=>Number.From(y[Colonna2]="")),
    #"Aggiunta colonna indice" = Table.AddIndexColumn(#"Raggruppate righe", "Indice", 1, 1, Int64.Type),
    #"Rimosse colonne" = Table.RemoveColumns(#"Aggiunta colonna indice",{"Colonna2"}),
    te = Table.ExpandTableColumn(#"Rimosse colonne", "all", {"Colonna1", "Colonna2"}, {"all.Colonna1", "all.Colonna2"}),
    cpb={0}&(List.RemoveLastN(te[Indice],1)),
    tfc=Table.FromColumns(Table.ToColumns(te)&{cpb},Table.ColumnNames(te)&{"NumberPreviuosBlankRows"}),
    #"Rimosse colonne1" = Table.RemoveColumns(tfc,{"Indice"})
in
    #"Rimosse colonne1"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

     

    I changed (again and hope for the last time)the script to manage both situations: first row empty e first row noEmpty.

     

    Try and let's know