Forum Discussion

Mederic's avatar
Mederic
Icon for Post Patron rankPost Patron
3 years ago
Solved

All PO positions are positive

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 pos...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    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"

     

DOLEARY85's avatar
DOLEARY85
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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 👍