Forum Discussion

hugoscp's avatar
hugoscp
Frequent Visitor
6 years ago
Solved

Max date by article

hello i have this data:   SOCDPR TPRDPR  ARTDPR  DTPDPR  PBCDPR  PDADPR 1 PD 287 0 3,52 28 1 PD 287 20100228 2,53 0 1 PD 287 20100826 3,52 20 1 PD 287 20100929 3,52 ...
Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago
    Did you try to sort the column of the Date Prior the Group-function?
  • hugoscp's avatar
    hugoscp
    6 years ago

    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
    GroupLast

     

     

    Thank you so much

hugoscp's avatar
hugoscp
Frequent Visitor
6 years ago

you put a Table.Buffer but unfortunately the result for the code 803 remain the same :(.

I cant understand why some results are corret and others dont.

Jimmy801's avatar
Jimmy801
Community Champion
6 years ago
Did you try to sort the column of the Date Prior the Group-function?
  • hugoscp's avatar
    hugoscp
    Frequent Visitor
    6 years ago

    No i did not sort nothing, do you want me to sort?

    • hugoscp's avatar
      hugoscp
      Frequent Visitor
      6 years ago

      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
      GroupLast

       

       

      Thank you so much