Create a Request with conditions for a column

[ANB_13]

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

[BA_Pete]

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]

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 · Accepted solution]

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