Forum Discussion
Max date by article
- Did you try to sort the column of the Date Prior the Group-function?
it work like this
let
Origem = Odbc.DataSource("dsn=inout.swapfilio", [HierarchicalNavigation=true]),
B606D19C_Database = Origem{[Name="B606D19C",Kind="Database"]}[Data],
SWAPFILIO_Schema = B606D19C_Database{[Name="SWAPFILIO",Kind="Schema"]}[Data],
GEFPR_Table = SWAPFILIO_Schema{[Name="GEFPR",Kind="Table"]}[Data],
#"Linhas Filtradas" = Table.SelectRows(GEFPR_Table, each [SOCDPR] = 1),
#"Linhas Filtradas1" = Table.SelectRows(#"Linhas Filtradas", each [TPRDPR] = "PD"),
#"Colunas Removidas" = Table.RemoveColumns(#"Linhas Filtradas1",{"MOEDPR", "CLIDPR", "PTEDPR", "MODDPR", "EMBDPR", "GFMDPR", "QESDPR", "DSFDPR", "DSVDPR", "QBBDPR", "BNSDPR", "IVADPR", "DTFDPR", "RGADPR", "TPDDPR", "DUADPR", "MTPDPR", "ZZ6"}),
#"Linhas Ordenadas" = Table.Buffer(Table.Sort(#"Colunas Removidas",{{"DTPDPR", Order.Ascending}})),
GroupLast = Table.Group
(
#"Linhas Ordenadas",
{"SOCDPR", "TPRDPR", "ARTDPR"},
{
{"LastRow DTPDPR", each List.Last([DTPDPR])},
{"LastRow PBCDPR", each List.Last([PBCDPR])},
{"LastRow PDADPR", each List.Last([PDADPR])}})
in
GroupLastThank you so much
Hello hugoscp
this would the adapted code... hoping that data structure is the same
let
Origem = Odbc.DataSource("dsn=inout.swapfilio", [HierarchicalNavigation=true]),
B606D19C_Database = Origem{[Name="B606D19C",Kind="Database"]}[Data],
SWAPFILIO_Schema = B606D19C_Database{[Name="SWAPFILIO",Kind="Schema"]}[Data],
GEFPR_Table = SWAPFILIO_Schema{[Name="GEFPR",Kind="Table"]}[Data],
#"Linhas Filtradas" = Table.SelectRows(GEFPR_Table, each [SOCDPR] = 1),
#"Linhas Filtradas1" = Table.SelectRows(#"Linhas Filtradas", each [TPRDPR] = "PD"),
#"Colunas Removidas" = Table.RemoveColumns(#"Linhas Filtradas1",{"MOEDPR", "CLIDPR", "PTEDPR", "MODDPR", "EMBDPR", "GFMDPR", "QESDPR", "DSFDPR", "DSVDPR", "QBBDPR", "BNSDPR", "IVADPR", "DTFDPR", "RGADPR", "TPDDPR", "DUADPR", "MTPDPR", "ZZ6"}),
GroupLast = Table.Group
(
#"Colunas Removidas",
{"SOCDPR", "TPRDPR", "ARTDPR"},
{
{"LastRow DTPDPR", each List.Last([DTPDPR])},
{"LastRow PBCDPR", each List.Last([PBCDPR])},
{"LastRow PDADPR", each List.Last([PDADPR])}})
in
GroupLast
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
thank you again for the reply.
In some case worked and others dont
one example that didnt work:
result:
SOCDPR TPRDPR ARTDPR LastRow DTPDPR LastRow PBCDPR LastRow PDADPR
| 1 | PD | 803 | 20180920 | 3,28 | 0 |
data from this article(ARTDPR)
SOCDPR TPRDPR ARTDPR DTPDPR PBCDPR PDADPR
| 1 | PD | 803 | 0 | 4,87 | 45 |
| 1 | PD | 803 | 20121127 | 4,87 | 45 |
| 1 | PD | 803 | 20130930 | 4,87 | 48 |
| 1 | PD | 803 | 20131128 | 4,87 | 48 |
| 1 | PD | 803 | 20140206 | 4,87 | 38 |
| 1 | PD | 803 | 20140217 | 4,87 | 38 |
| 1 | PD | 803 | 20150108 | 4,87 | 50 |
| 1 | PD | 803 | 20160327 | 4,87 | 45 |
| 1 | PD | 803 | 20160413 | 4,87 | 50 |
| 1 | PD | 803 | 20161111 | 4,87 | 45 |
| 1 | PD | 803 | 20161215 | 4,87 | 50 |
| 1 | PD | 803 | 20170120 | 4,87 | 39 |
| 1 | PD | 803 | 20180110 | 4,87 | 35 |
| 1 | PD | 803 | 20180405 | 5,6 | 35 |
| 1 | PD | 803 | 20180411 | 5,6 | 35 |
| 1 | PD | 803 | 20180920 | 3,28 | 0 |
| 1 | PD | 803 | 20181011 | 5,6 | 41,96 |
| 1 | PD | 803 | 20181016 | 5,6 | 35 |
should be:
SOCDPR TPRDPR ARTDPR LastRow DTPDPR LastRow PBCDPR LastRow PDADPR
| 1 | PD | 803 | 20181016 | 5,6 | 35 |
Hello hugoscp
my code works just fine. I applied my logic to your new data example. And my output is the one expected
maybe you are sorting the table before?
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
Jimmyi am not sorting. I dont know why some work and others dont