Power Query Challenge

[sathyasri]

Col A [Carrier Name] : Conway, FedeEx, FedEx,

Col B [ Rate] :$400, $500, $800

Col C[ Transit Time]: 5 Days, 4 Days, 3 Days

Desired Result :

Col A [Carrier Name: Conway "Standard", FedEx "Standard" , FedEx "Priority"

Col B [Rate] :$400, $500, $800

Col C [Transit Time] :5 Days, 4 Days, 3 Days

In other words, if the carrier is same,but higher rate and faster transit time should add the word "Piroirity" to the carrier name.

[Jimmy801]

Hello @sathyasri 

check out this solution. Uses Table.Group and in the function applied the rows are changed according your requirements. Transit time is the lowest value and Rate the highest

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcktNSa1Q0lGyMDAAksZKsToIMVOwmClYzDk/rzyxElkwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Carrier name" = _t, Rate = _t, #"Transit time" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Carrier name", type text}, {"Rate", Int64.Type}, {"Transit time", Int64.Type}}),
    #"Grouped Rows" = Table.Group
    (
        #"Changed Type", 
        {"Carrier name"}, 
        {
            {
                "AllRows", 
                (tableint)=> if Table.RowCount(tableint)=1 then                 Table.FromRecords(Table.TransformRows(tableint, (rec)=>  Record.TransformFields(rec, {{"Carrier name",each rec[Carrier name] & " Standard"}} )))
                else 
                Table.FromRecords(Table.TransformRows(tableint, (rec)=> if (rec[Rate]=List.Max(tableint[Rate]) and rec[Transit time]=List.Min(tableint[Transit time])) then Record.TransformFields(rec, {{"Carrier name",  each rec[Carrier name] & " Priority"}} ) else Record.TransformFields(rec, {{"Carrier name",each rec[Carrier name] & " Standard"}} ))), 
                type table [Carrier name=text, Rate=number, Transit time=number]}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"AllRows"}),
    GetColumnsList = List.Distinct(List.Combine(Table.TransformColumns(#"Removed Other Columns", {{"AllRows", each Table.ColumnNames(_)}})[AllRows])),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Removed Other Columns", "AllRows", GetColumnsList, GetColumnsList)
in
    #"Expanded AllRows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

There are some things that are not clear to me, perhaps because I don't know English well.
The first.
When you say "faster time" you mean the shortest time right?
If so, the problem is wrong posed, in the sense that if you impose two independent conditions on two different columns you may not find any row that satisfies both.
If instead they are not independent, that is, if ALWAYS the "faster time" occurs when there is the "higher rate" then the conditions you impose are redundant and only one of the two would be enough.
Otherwise, as an alternative, if you need to evaluate both conditions you should define a cost function dependent on these parameters and impose the optimization of this.
The second.
I don't understand the meaning of the title. I believe that if you had made it more adherent to the content of your need, the system would automatically have given you a series of links in which a list of problems equal or similar to yours have been discussed and solved and from these also find your solution .