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...
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

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
      Icon for Super User rankSuper 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