All PO positions are positive

Hello, Mederic 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdRXjgMhDADQu8x3RtiYMnxutvdeo9z/GgsGJoNlKRtt0IvHjRwO05IAAP20mzD/WTRAxgKG/OFsf57fHZV/p+Nua61ugxM2tbiwGPDdXlxe5XcqMfbCljOMBrDYuNoZnYJJx/wMaV2zabROJGFjTxgNQrfXN7flcaI4tla3cRF2aXHRGuR8y8Hd/QMfgYK5w7YVlzZYsaTbmYKCuRPB5ESGLObgR8zz4ZR9xZzm49NzmXMc2+ZDt1R3gu3zyysfeQVzeWDAjtiKOce1b0uz5cPb+4fS49jbptgZvYJJx04MOvXbQVi32NrTxoviUr8dEEx+DZbG4hBsb0S2sdu6QHLjK/Y6jiQstTnnyngz2X5+fZdjHIpD6MXloLyZbGsjAinW6tbLuOtKtF3b2BRFvutNorrEtjz45/evfN2JuLHXltqqnWxahO3bk6dWh0zrCoubj7D+/CybuPVueJlvarPAbdxqqc3i+A8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PO = _t, POS = _t, #"Last Modification" = _t, Vendor = _t, Amt = _t, Cat = _t]),
    ToDates = Table.TransformColumnTypes(Source, {{"Last Modification", type date}, {"Amt", type number}}, "hr-HR"),
    #"Grouped Rows" = Table.Combine (Table.Group(ToDates, {"PO"}, {{"A", each let t=_ in Table.AddColumn( Table.AddColumn(t, "Last Mod fixed", each List.Max (t[Last Modification]), type date), "Splice", each let x=Number.Round(Number.From((Date.From(DateTime.LocalNow()) - [Last Mod fixed])/( 365.25 / 12 )) ,0 ) in if x >12 then ">1 Year" else if x >6 then ">6 Months" else Text.From(Date.Year([Last Mod fixed])) & "." & Text.From(Date.Month([Last Mod fixed]) ) ) , type table }})[A]),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"PO"}, {{"rows", each _, type table [PO=nullable text, POS=nullable text, Last Modification=nullable date, Vendor=nullable text, Amt=nullable number, Cat=nullable text, Last Mod fixed=date, Splice=text]}, {"All PO Positions are Positive", each if List.Min([Amt]) >= 0 then "true" else "false", type nullable text}}),
    #"Expanded rows" = Table.ExpandTableColumn(#"Grouped Rows1", "rows", {"POS", "Last Modification", "Vendor", "Last Mod fixed", "Splice", "Amt", "Cat"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded rows",{"PO", "POS", "Last Modification", "Vendor", "Last Mod fixed", "Splice", "Amt", "All PO Positions are Positive", "Cat"})
in
    #"Reordered Columns"

Hi,

This is possible in a few steps:

1. create a column that counts if Amt column is greater than 0 if yes bring back 1 else 0
2. Duplicate the table
3. in the duplicate table use group by and choose advanced
4. Group on PO column
5. add 2 rows of aggregation: 1. count - count rows 2. sum - sum of the column you created in that counts if Amt if greater than 0
6. Add another new column to the duplicated table - if count rows = sum of Amt count then "True" else "False"
7. Go back to the original table and select merge queries
8. merge on only the PO column in both tables
9. Expand the new table field and only select the true / false colum you created

this should bring through the required data

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Hello DOLEARY85 ,

Thank you for your solution which does indeed work,
However, I'm more of a fan of short codes, preferably in one step,
I already have a lot of queries and steps in my real file, I try to make it as short as possible, like in the solution of Jakinta 

Thanks in advance

Regards

Hello AlienSx ,

Thank you very much for your solution which is perfect,
However, I wanted to integrate your formula into the previous step to form a single step
I thought of a code like the one below but without success,
However, I would like to integrate the column [All PO Positions are Positive] inside the column [A] (see screenshot)
Then use Table.Combine() to expand the list [A] without going through an "Expended rows" step

Thanks in advance
Regards

= Table.Group(ToDates, {"PO"}, {
{"A", each let t=_ in Table.AddColumn( Table.AddColumn(t, 
"Last Mod fixed", each List.Max (t[Last Modification]), type date), 
"Splice", each let x=Number.Round(Number.From((Date.From(DateTime.LocalNow()) - [Last Mod fixed])/( 365.25 / 12 )) ,0 ) 
	in if x >12 then ">1 Year" else if x >6 then ">6 Months" 
	else Text.From(Date.Year([Last Mod fixed])) & "." & Text.From(Date.Month([Last Mod fixed]) ) ) , type table },
{"All PO Positions are Positive", each if List.Min([Amt]) >= 0 then "true" else "false", type text}
}
)

AlienSx ,

Excellent, thank you very much for your help, this works very well

Have a nice day