Forum Discussion
Solved
Two values against sales, return only 1
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 Ord...
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
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"
That is really smart, thanks!!