Forum Discussion

Tob_P's avatar
Tob_P
Helper V
2 years ago
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...
Need Help
  • Chewdata's avatar
    Chewdata
    2 years ago

    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's avatar
bhanu_gautam
Super User
2 years ago

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's avatar
    Tob_P
    Helper V
    2 years ago

    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?