Result Selection out of multiple lines - related to specific criteria

[Syndicate_Admin]

Dear Community,

hope this post finds you well , as i'm not able to find the proper solution.

I do have a Query that provides me an overview similar to the attached file.

Aim is to find the upcoming valuation for a new period - based on different criteria.

Explanation:

IF there is a Quantity on hand i want to receive the price of the location with highest quantity

IF there is no Quantity it is to be checked if there is a last valuation instead - if so - this one to be taken

IF both are not available but there was a calculation done (means a condition is set), this is to be taken

IF all three are not on hand, i want to refer to old pricing.

I tried to get the result with conditional column but in the end i do have 6 line (or pending on the object 1 to x lines), but only want to get one single line

Does anybody has a hint , an idead or solution how i can fix that? I'm trying to use this in Excel Power Query

thanks in advance, any input is appreciated

BR CIM1234

[Syndicate_Admin]

thanks issue solved. appreciated

[Anonymous]

HI @Syndicate_Admin 

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards!

Yolo Zhu

[Anonymous · Accepted solution]

Hi @Syndicate_Admin 

Based on the understanding of your description, do you want to get the first row that satisfies the criteria, such as the first one :Locationp1, it  satisfies the first condition you described, that is, to return the line p1?

please refer to the following code, the code is based on the condition column [Condition Pricing] you have created.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRcgRiBQsDPQMDBSDLNTQISBoCcYAziACxDC3ACi1BBISO1SFOtxGQAKkyhWq1IF6rMYhnaoCs25R43SZQi01It9iUfDebAQljDCfHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Part = _t, Name = _t, #"Price old" = _t, Currency = _t, PUoM = _t, BUoM_eng = _t, Location = _t, Quantity = _t, #"Price Location" = _t, #"Last Valuation" = _t, #"Condition Pricing" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part", Int64.Type}, {"Name", type text}, {"Price old", Int64.Type}, {"Currency", type text}, {"PUoM", Int64.Type}, {"BUoM_eng", type text}, {"Location", type text}, {"Quantity", Int64.Type}, {"Price Location", Int64.Type}, {"Last Valuation", type text}, {"Condition Pricing", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Part", "Name"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1),type table}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Price old", "Currency", "PUoM", "BUoM_eng", "Location", "Quantity", "Price Location", "Last Valuation", "Condition Pricing", "Index"}, {"Price old", "Currency", "PUoM", "BUoM_eng", "Location", "Quantity", "Price Location", "Last Valuation", "Condition Pricing", "Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Count", each ([Index] = List.Min(Table.SelectRows(#"Expanded Count",(x)=>x[Part]=[Part] and x[Name]=[Name] and x[Condition Pricing]<>null)[Index]))),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.