find minimum value of a group and display 2 relevant columns

[casamontagna]

Dears,

i am sure there is a simple solution, but i am struggling on the following problem:

I need to find the the first order date of a budget (budget = customer order in  a project) in a project. So far not a problem, i would be only searching the MIN date of a project  by grouping

. My problem is, that i need to have the information of the budget number and the project. In other words, i need only one row for each project, showing ProjectNr, budget and orderdate. if two or more budgets in a project do have the same order date, it is enough to consider just one of those.

thank you.

[Jimmy801 · Accepted solution]

Hello @casamontagna 

just add a new grouping criteria that picks up the first budget. To get the right sort order of the budget numer, sort the date in ascending and surround it with table.buffer. Here a code example

let
	Source = #table
	(
		{"ProjectNr","budget","orderdate"},
		{
			{"A","1","43854"},	{"A","2","43853"},	{"B","1","43842"},	{"B","2","43843"}
		}
	),
    ToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "orderdate",
                each Date.From(Number.From(_)),
                type date
            }
        }
    ),
    SortOrderDateAscending = Table.Buffer
    (
        Table.Sort
        (
            ToDate,
            {{"orderdate", Order.Ascending}}
        )
    ),
    GroupSpecial = Table.Group
    (
        SortOrderDateAscending, 
        {"ProjectNr"}, 
        {{"MinOrderDate", each List.Min([orderdate]), type date}, {"firstBudget", each List.First(_[budget])}}
    )
in
	GroupSpecial

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[casamontagna]

@Jimmy801  thank yo so much! made my day and saved me from futher headache 🙂

[Jimmy801]

Hello @casamontagna 

thanks for the feedback. It's very appreciated

Jimmy