Forum Discussion

ANB_13's avatar
ANB_13
Icon for Helper I rankHelper I
3 years ago
Solved

Create a Request with conditions for a column

Hello everyone,    Please i need your help, I have some data in two columns and i want to create a request with conditions : In the title column :  ARB 1, ARB 2 etc... i want to have the same "Nb...
  • BA_Pete's avatar
    BA_Pete
    3 years ago

     

    Ah, ok. Try this:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Lc27DcAgDAXAVSLXFMGGfEoTZQG6CLH/GsF+Lq+6MYgrJdK300wLh6E/wOn4gMvRtgzeQXbKHhQwBwvIwQrKYlMFisH6+QM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Nb = _t, Title = _t]),
        chgTypes = Table.TransformColumnTypes(Source,{{"Nb", Int64.Type}, {"Title", type text}}),
    
    // Relevant steps from here:
        addTitleGroup = Table.AddColumn(chgTypes, "titleGroup", each Text.Select([Title], {"A".."Z"})),
        groupRows = Table.Group(addTitleGroup, {"titleGroup"}, {{"data", each _, type table [Nb=nullable number, Title=nullable text, titleGroup=text]}}),
        addCalcNewNb = Table.AddColumn(groupRows, "calcNewNb", each List.Min([data][Nb])),
        expandDataCol = Table.ExpandTableColumn(addCalcNewNb, "data", {"Title", "Nb"}, {"Title", "Nb"})
    in
        expandDataCol

     

    To get this output:

     

     

    Pete