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
Hey!
This is how I would solve this.
- Create score column Approved = 1, pending = 0.
- Group columns, add columns: Count, sum of score and All rows.
- Add new Approved/Pending column. If Score = Count then approved, else Pending.
- Remove unnessecary columns
- 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!
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