Forum Discussion
Solved
Last date by order and classification by period
Hello everyone,
I had opened a message here in the DAX section for my problem. Finally, I am wondering if it would be easier with Power query by adding 2 new columns to my Source table (see screenshot)
I'm more comfortable with Power query than DAX, maybe that's what makes me go for a Power query solution,
Can you please help me with this problem?
Thank 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
This might help.
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"
2 Replies
This might help.
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"
