All PO positions are positive

[Mederic]

Hello everyoone,

As a follow up to my previous thread here, I would still need your help please.
I would like to add a "True or False" column depending on whether all positions in the order are positive or not (see screenshot)
Thanking you in advance

Regards

PO	POS	"Last  
Modification"	Vendor	Amt	Cat
8900015	1	21/03/2016	ABC	43	A
8900015	2	21/03/2016	ABC	64	A
8900019	1	08/05/2016	DEF	32	B
8900019	2	17/01/2017	DEF	-14	B
8900019	3	17/01/2017	DEF	16	B
8900019	4	17/09/2017	DEF	42	B
8900027	1	01/10/2017	GHI	34	A
8900027	2	01/10/2017	GHI	78	A
8900028	1	12/11/2018	JKL	120	A
8900028	2	22/01/2019	JKL	12	A
8900028	3	22/01/2019	JKL	-36	A
8900028	4	16/12/2018	JKL	-65	A
8900032	1	15/12/2020	MNO	67	B
8900056	1	13/03/2020	OPQ	135	B
8900056	2	20/02/2020	OPQ	24	B
8900078	1	18/02/2021	RST	78	A
8900078	2	18/02/2021	RST	-15	A
8900078	3	18/02/2021	RST	44	A
8900095	1	31/05/2022	ABC	65	B
8900095	2	06/06/2022	ABC	34	B
8900102	2	26/07/2022	GHI	-14	B
8900102	5	26/07/2022	GHI	73	B
8900103	4	02/10/2022	UVW	41	A
8900105	1	07/11/2022	RST	63	A
8900105	2	07/11/2022	RST	51	A
8900106	1	16/12/2022	RST	97	B
8900107	2	03/01/2023	XYZ	14	A
8900107	4	09/03/2023	XYZ	98	A
8900108	1	05/02/2023	MNO	42	B
8900109	2	18/03/2023	JKL	57	B
8900109	5	15/02/2023	JKL	33	B

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}}, "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]),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"PO", "POS", "Last Modification", "Vendor", "Last Mod fixed", "Splice", "Amt", "Cat"})
in
    #"Reordered Columns"

[Mederic]

@AlienSx ,

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

Have a nice day

[Mederic]

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 · Accepted solution]

sure. I applied some formatting to not get lost. Please note that I also had to set Amt column type to number (ToDates step). 

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( 
                  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]) ) 
                  ),
                "All PO Positions are Positive", each if List.Min(t[Amt]) >= 0 then "true" else "false", type text
              ), 
            type table 
          }})[A]),
    #"Reordered Columns" = Table.ReorderColumns(#"Grouped Rows",{"PO", "POS", "Last Modification", "Vendor", "Last Mod fixed", "Splice", "Amt", "All PO Positions are Positive", "Cat"})
in
    #"Reordered Columns"

[AlienSx]

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"

[Mederic]

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

[DOLEARY85]

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 👍