Forum Discussion

ANB_13's avatar
ANB_13
Helper 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"... 

What is the request in power query can i do ? 

 

Thank you very much

Best regards

 

Need Help
Tutorial Requests
  • 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

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    Hi ANB_13 ,

     

    I don't really understand what you're asking for here. Could you provide an example of what your expected output looks like please?

     

    Thanks,

     

    Pete

    • ANB_13's avatar
      ANB_13
      Helper I
      3 years ago

      I want to create a conditional column that says that if the 'title' is the same with only the number at the end differing (1,2,3...), then we use only one 'Nb' : for exemple : 

       

      But not only for this example "ARB", even for others if they exist

      Thank youuuu

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        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