Dear all,
in my app, I want to get the latest data from my sharepoint list for specific Customer and period levels :
I got this store in the sharepoin list
ID CUSTOMER PERIOD IND_VALUE 2 C003 2018.07 500 3 C003 2018.08 450 4 C003 2018.09 350 5 C003 2018.10 300 6 C003 2018.11 350 7 C003 2018.12 420 803 C003 2019.01 250 805 C003 2019.01 550 809 C003 2018.09 420 804 C003 2018.11 300
and I want to get this :
ID CUSTOMER PERIOD IND_VALUE 2 C003 2018.07 500 3 C003 2018.08 450 809 C003 2018.09 420 5 C003 2018.10 300 804 C003 2018.11 300 7 C003 2018.12 420 805 C003 2019.01 550
Does someone know how I can tackle it ? (I tried function table, addcolumn, filter without success)
in advance thanks for your feedback.
Novice
Hello Kris,
Thanks a lot, it works perfectly
Hi @novicenovice,
Could you please share a bit more about the ID column and the PERIOD column in your SP list?
Is the ID column a System column or a custom column? Is the ID column a Number type column?
I assume that the ID column is a Number type column within your SP list, I have made a test on my side, please take a try with the following workaround:
Set the Items property of the Data table control to following formula:
DropColumns(
AddColumns(
GroupBy(SortByColumns('20180728_case11',"OData__x0049_D1",Ascending),"PERIOD","GroupData"),
"ID",Last(GroupData).OData__x0049_D1,
"CUSTOMER",Last(GroupData).CUSTOMER,
"IND_VALUE",Last(GroupData).IND_VALUE
),
"GroupData"
)
Note: The "OData__x0049_D1" represents the ID1 column in my SP list data source (on your side, it is ID column).
On your side, please type the following formula:
DropColumns( AddColumns( GroupBy(SortByColumns('YourSPListDataSource',"ID",Ascending),"PERIOD","GroupData"), "ID",Last(GroupData).ID, "CUSTOMER",Last(GroupData).CUSTOMER, "IND_VALUE",Last(GroupData).IND_VALUE ), "GroupData" )
Best regards,
Kris