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

    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

      • 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"