Multiple columns return header of cheapest

[SJ]

 Hi,

I am trying to identify the order of information from several columns (Below 10 Total to 90 Total).

For the first "Delivery ID - Post" below "10 Total" is the cheapest cost thus in "Cost 1" I would like to return the header "10 Total".

In "Cost 2" I would like to return the second cheapest i.e. "50 Total" and in Cost 3 the 3rd cheapest i.e. "20 Total". 

Can anyone help me with the logic on required for this?

[v-yulgu-msft · Accepted solution]

Hi @SJ,

You could achieve this output with M code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Sample Data.xlsx"), null, true),
    #"ID post_Sheet" = Source{[Item="ID post",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"ID post_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Delivery ID Post", type text}, {"10 Total", Int64.Type}, {"20 Total", Int64.Type}, {"50 Total", Int64.Type}, {"60 Total", Int64.Type}, {"70 Total", Int64.Type}, {"80 Total", Int64.Type}, {"90 Total", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Delivery ID Post"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Total"}}),
    #"Grouped Rows1" = Table.Group(#"Renamed Columns", {"Delivery ID Post"} , {{"All Rows", each Table.Sort(_ ,{{"Value", Order.Ascending}}), type table}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "All Rows", {"Total", "Value"}, {"All Rows.Total", "All Rows.Value"}),
    #"Grouped Rows2" = Table.Group( #"Expanded All Rows" , {"Delivery ID Post"} , {{"All Rows2", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded All Rows2" = Table.ExpandTableColumn(#"Grouped Rows2", "All Rows2", {"All Rows.Total", "All Rows.Value", "Index"}, {"All Rows2.All Rows.Total", "All Rows2.All Rows.Value", "All Rows2.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded All Rows2", "Custom", each if [All Rows2.Index] = 1 then "Cost1" else if [All Rows2.Index] = 2 then "Cost2" else if [All Rows2.Index] = 3 then "Cost3" else "Cost4"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [All Rows2.Index] = 1 then [All Rows2.All Rows.Value] else if [All Rows2.Index] = 2 then [All Rows2.All Rows.Value] else if [All Rows2.Index] = 3 then [All Rows2.All Rows.Value] else 0),
    #"Pivoted Column" = Table.Pivot(#"Added Conditional Column1", List.Distinct(#"Added Conditional Column1"[#"All Rows2.All Rows.Total"]), "All Rows2.All Rows.Total", "All Rows2.All Rows.Value", List.Sum),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Custom.1", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column1",{"All Rows2.Index", "Cost4"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Delivery ID Post"}, {{"10 Total", each List.Sum([10 Total]), type number}, {"20 Total", each List.Sum([20 Total]), type number}, {"50 Total", each List.Sum([50 Total]), type number}, {"60Total", each List.Sum([60 Total]), type number}, {"70 Total", each List.Sum([70 Total]), type number}, {"80 Total", each List.Sum([80 Total]), type number}, {"90 Total", each List.Sum([90 Total]), type number}, {"Cost1", each List.Sum([Cost1]), type number}, {"Cost2", each List.Sum([Cost2]), type number}, {"Cost3", each List.Sum([Cost3]), type number}})
in
    #"Grouped Rows"

I have uploaded the .pbix file for your reference.

Best regards,

Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @SJ,

You could achieve this output with M code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxx\Desktop\Sample Data.xlsx"), null, true),
    #"ID post_Sheet" = Source{[Item="ID post",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"ID post_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Delivery ID Post", type text}, {"10 Total", Int64.Type}, {"20 Total", Int64.Type}, {"50 Total", Int64.Type}, {"60 Total", Int64.Type}, {"70 Total", Int64.Type}, {"80 Total", Int64.Type}, {"90 Total", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Delivery ID Post"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Total"}}),
    #"Grouped Rows1" = Table.Group(#"Renamed Columns", {"Delivery ID Post"} , {{"All Rows", each Table.Sort(_ ,{{"Value", Order.Ascending}}), type table}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows1", "All Rows", {"Total", "Value"}, {"All Rows.Total", "All Rows.Value"}),
    #"Grouped Rows2" = Table.Group( #"Expanded All Rows" , {"Delivery ID Post"} , {{"All Rows2", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded All Rows2" = Table.ExpandTableColumn(#"Grouped Rows2", "All Rows2", {"All Rows.Total", "All Rows.Value", "Index"}, {"All Rows2.All Rows.Total", "All Rows2.All Rows.Value", "All Rows2.Index"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded All Rows2", "Custom", each if [All Rows2.Index] = 1 then "Cost1" else if [All Rows2.Index] = 2 then "Cost2" else if [All Rows2.Index] = 3 then "Cost3" else "Cost4"),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [All Rows2.Index] = 1 then [All Rows2.All Rows.Value] else if [All Rows2.Index] = 2 then [All Rows2.All Rows.Value] else if [All Rows2.Index] = 3 then [All Rows2.All Rows.Value] else 0),
    #"Pivoted Column" = Table.Pivot(#"Added Conditional Column1", List.Distinct(#"Added Conditional Column1"[#"All Rows2.All Rows.Total"]), "All Rows2.All Rows.Total", "All Rows2.All Rows.Value", List.Sum),
    #"Pivoted Column1" = Table.Pivot(#"Pivoted Column", List.Distinct(#"Pivoted Column"[Custom]), "Custom", "Custom.1", List.Sum),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column1",{"All Rows2.Index", "Cost4"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Delivery ID Post"}, {{"10 Total", each List.Sum([10 Total]), type number}, {"20 Total", each List.Sum([20 Total]), type number}, {"50 Total", each List.Sum([50 Total]), type number}, {"60Total", each List.Sum([60 Total]), type number}, {"70 Total", each List.Sum([70 Total]), type number}, {"80 Total", each List.Sum([80 Total]), type number}, {"90 Total", each List.Sum([90 Total]), type number}, {"Cost1", each List.Sum([Cost1]), type number}, {"Cost2", each List.Sum([Cost2]), type number}, {"Cost3", each List.Sum([Cost3]), type number}})
in
    #"Grouped Rows"

I have uploaded the .pbix file for your reference.

Best regards,

Yuliana Gu

[SJ · Accepted solution]

I may see this wrong but my understanding is that M code is only used at the import stage. The fields in my report are calcualted in DAX. If you have if statements for now but it does seem quite heavy way of doing this.

Thanks for your help anyway.