Two values against sales, return only 1

[Tob_P]

Hi,

I have sales orders that are either approved or pending. A sales order in some instances can be either of those but for the purposes of a custom column, I would like to look at each Sales Order No and if it both approved and pending, then I would like all rows corresponding to that Sales Order No to return Pending, but if it only contains Approved, then return approved and if it only contains Pending, then return pending.

Sample data:

Sales Order No	Approved/Pending
SO707125	Pending
SO707125	Approved
SO707778	Pending
SO707778	Approved
SO707778	Approved
SO707778	Approved
SO707778	Approved
SO707778	Approved
SO707778	Approved
SO708310	Pending
SO708310	Approved
SO708310	Approved
SO708310	Approved
SO708310	Approved
SO711592	Pending
SO711592	Approved
SO711592	Approved
SO744581	Approved
SO744581	Approved
SO779586	Pending
SO779586	Pending

Desired result...

Sales Order No	Approved/Pending
SO707125	Pending
SO707125	Pending
SO707778	Pending
SO707778	Pending
SO707778	Pending
SO707778	Pending
SO707778	Pending
SO707778	Pending
SO707778	Pending
SO708310	Pending
SO708310	Pending
SO708310	Pending
SO708310	Pending
SO708310	Pending
SO711592	Pending
SO711592	Pending
SO711592	Pending
SO744581	Approved
SO744581	Approved
SO779586	Pending
SO779586	Pending

Any guidance appreciated!

[p45cal]

Another

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3NzA3NDJV0lEKSM1LycxLV4rVQRF2LCgoyi9LTUGIm5tbYFMOEcalnO7iFsaGBlicCRXGpZxScUNDU0sjTGthwriUo4ubmJhaGJIgbm5pamGGaS2GcCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order No" = _t, #"Approved/Pending" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Sales Order No", type text}, {"Approved/Pending", type text}}),
    GroupedRows = Table.Group(ChangedType, {"Sales Order No"}, {{"AllRows",each _[#"Sales Order No"]},{"Approved/Pending", each if List.Contains(_[#"Approved/Pending"],"Pending") then "Pending" else "Approved"}}),
    ExpandedAllRows = Table.ExpandListColumn(GroupedRows, "AllRows"),
    RemovedColumns = Table.RemoveColumns(ExpandedAllRows,{"AllRows"})
in
    RemovedColumns

[ThxAlot]

Easy enough with calculated column,

[p45cal]

@Chewdata, an observation, not a criticism; you could eliminate the Count column by reversing the 0/1 in the add_score step, don't create a count column in the GroupedRows step and change the if in the add_approvedpending step to read each if [Approved] > 0 then "Pending" else "Approved"

[Chewdata]

That is really smart, thanks!!

[Chewdata]

Hey!

This is how I would solve this.

1. Create score column Approved = 1, pending = 0.
2. Group columns, add columns: Count, sum of score and All rows.
3. Add new Approved/Pending column. If Score = Count then approved, else Pending.
4. Remove unnessecary columns
5. Expand the table to get all rows back.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3NzA3NDJV0lEKSM1LycxLV4rVQRF2LCgoyi9LTUGIm5tbYFMOEcalnO7iFsaGBlicCRXGpZxScUNDU0sjTGthwriUo4ubmJhaGJIgbm5pamGGaS2GcCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order No" = _t, #"Approved/Pending" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Sales Order No", type text}, {"Approved/Pending", type text}}),
    add_score = Table.AddColumn(ChangedType, "Score", each if [#"Approved/Pending"] = "Approved" then 1 else 0, Int64.Type),
    GroupedRows = Table.Group(add_score, {"Sales Order No"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Approved", each List.Sum([Score]), type number}, {"Table", each _, type table [Sales Order No=nullable text, #"Approved/Pending"=nullable text, Score=number]}}),
    add_approvedpending = Table.AddColumn(GroupedRows, "Approved/Pending", each if [Approved] = [Count] then "Approved" else "Pending", type text),
    RemovedColumns = Table.RemoveColumns(add_approvedpending,{"Sales Order No", "Count", "Approved"}),
    ExpandedTable = Table.ExpandTableColumn(RemovedColumns, "Table", {"Sales Order No"}, {"Sales Order No"})
in
    ExpandedTable

 Hopefully this is helpfull!

[Chewdata · Accepted solution]

Using the feedback from @p45cal, here the adjusted code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCvY3NzA3NDJV0lEKSM1LycxLV4rVQRF2LCgoyi9LTUGIm5tbYFMOEcalnO7iFsaGBlicCRXGpZxScUNDU0sjTGthwriUo4ubmJhaGJIgbm5pamGGaS2GcCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Sales Order No" = _t, #"Approved/Pending" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Sales Order No", type text}, {"Approved/Pending", type text}}),
    add_score = Table.AddColumn(ChangedType, "Score", each if [#"Approved/Pending"] = "Approved" then 0 else 1, Int64.Type),
    GroupedRows = Table.Group(add_score, {"Sales Order No"}, {{"Approved", each List.Sum([Score]), type number}, {"Table", each _, type table [Sales Order No=nullable text, #"Approved/Pending"=nullable text, Score=number]}}),
    add_approvedpending = Table.AddColumn(GroupedRows, "Approved/Pending", each if [Approved] > 0 then "Pending" else "Approved", type text),
    RemovedColumns = Table.RemoveColumns(add_approvedpending,{"Sales Order No", "Approved"}),
    ExpandedTable = Table.ExpandTableColumn(RemovedColumns, "Table", {"Sales Order No"}, {"Sales Order No"})
in
    ExpandedTable

[bhanu_gautam]

@Tob_P , Here is the power query to achieve desired output

let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    GroupedRows = Table.Group(Source, {"Sales Order No"}, {{"All Statuses", each _, type table [Sales Order No=nullable text, Approved/Pending=nullable text]}}),

    AddedCustom = Table.AddColumn(GroupedRows, "Final Status", each if List.Contains([All Statuses][Approved/Pending], "Pending") then "Pending" else "Approved"),

    ExpandedRows = Table.ExpandTableColumn(AddedCustom, "All Statuses", {"Sales Order No", "Approved/Pending"}),

    ReplacedValues = Table.ReplaceValue(ExpandedRows, each [Approved/Pending], each [Final Status], Replacer.ReplaceValue, {"Approved/Pending"}),

    RemovedColumns = Table.RemoveColumns(ReplacedValues,{"Final Status"})

in

    RemovedColumns

[Tob_P]

Hi @bhanu_gautam 

Thank you for this - have tried within my original .pbix file and on a new file with just the sample data and there seems to be an error at AddedCustom = Table.AddColumn(GroupedRows, "Final Status", each if List.Contains([All Statuses][Approved/Pending], "Pending") then "Pending" else "Approved"),

...the highlighted section. It doesn't recognise it as a column. Can I ask if all the steps are in the correct order?