cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jamesantony
Level: Powered On

Sort by date is not working - please help

Hello Experts,

 

I have 2 tables one to get hte data from SharePoint list and other to take only needed columns with grouping.

in the second table I need to sort it based on dates so that the line graph will show in the order of the dates..

 

right now it is sorting based on text.. but the column is already in date format. dont know where is the problem.

My expectation is to show the app usage by date for the last 10 days may be.. and I am trying to use the modified date to work around.. of you have any other suggestion also please let me know.

 

Thank you all.

 

please help.

 

First Table:

ClearCollect(UsageTrend, 'Lessons Learned Library'.Modified, 'Lessons Learned Library'.Test_Field); /*Get the values into a master Table*/
ClearCollect(UsageTrend, AddColumns('Lessons Learned Library', "Usage_Date", Text(Modified, DateTimeFormat.ShortDate))); /*Take only the date*/

 

Second Table:
ClearCollect(UsageCollect, AddColumns(GroupBy(UsageTrend, "Usage_Date", "Grouped"), "Nums", CountRows(Grouped))); 
ClearCollect(UsageCollect, AddColumns(UsageTrend, "Dateform", DateValue("Usage_Date", DateTimeFormat.ShortDate)));
ClearCollect(UsageCollect, SortByColumns(UsageCollect, "Usage_Date"))

2 ACCEPTED SOLUTIONS

Accepted Solutions
Meneghino
Level 10

Re: Sort by date is not working - please help

You are right.  Try this please:

/*First Table:*/
ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values of two columns only into a master table*/
 
/*Second Table:*/
ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(
	AddColumns(UsageTrend, "MyDate", Date(Year(Modified), Month(Modified), Day(Modified))),
	"MyDate", "Grouped"), "Nums", CountRows(Grouped)), "Modified", Descending))

 

You can format the date as text when you have to display it, like this:

Text(MyDate, "mm/dd/yyyy")

 

View solution in original post

jamesantony
Level: Powered On

Re: Sort by date is not working - please help

So kind of you @Meneghino

 

Great responses. I updated like this and it worked like charm. It was actually the text format that caused the issue. If I put Date format with long date, short date etc., it is not working. With this format"mm/dd/yyyy" it worked like a charm.

 

is there a way to get the data labels in the graph? alos is it possible to change the line color and thickness?

I see the item colorset but there is a lot... not sure whichone represents the line.

 

 

 

/*Colelction for Usage Trend Graph*/

ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values into a master Table*/
ClearCollect(UsageTrend, AddColumns('Lessons Learned Library', "Usage_Date", Text(Modified, "[$-en-US]mm/dd/yyyy"))); /*Take only the date*/

ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Usage_Date", "Grouped"), "Nums", CountRows(Grouped)),"Usage_Date", Ascending))

 

View solution in original post

8 REPLIES 8
Meneghino
Level 10

Re: Sort by date is not working - please help

Hello @jamesantony

I am not sure I follow your code.  The sequential steps should be nested instead of being in series.

Also, the first ClearCollect seems to be collecting two one-row tables one after the other.

You probably need something like this:

 

/*First Table:*/
ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values of two columns only into a master table*/
 
/*Second Table:*/
ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Modified", "Grouped"), "Nums", CountRows(Grouped)), "Modified"))

If you want to change the name of the "Modified" column you just nest a RenameColumns function.

jamesantony
Level: Powered On

Re: Sort by date is not working - please help

@Meneghino thank you for your quick response.

 

I have a sharepoint list - "Library"

I cannot use the data as is for the line chart because I need to group the count based on Modified date. [which has time stamp also] but I need only Date.

So trying to create a temp collection.

First table I got the records from Sharepoint list. formatted the date column as needed and group the record count column created.

Second table - I need to sort the data in the date desending order.

 

Hope thsi explains my situation.. let me know if you need more details.

Meneghino
Level 10

Re: Sort by date is not working - please help

It does explain, thank you.

Does my suggestion improve the situation?

Do you understand where I see issues with your code?

jamesantony
Level: Powered On

Re: Sort by date is not working - please help

It is not working yet.. 

First I need to format the date column to short date. mm/dd/yyyy [datetimevalue("Modified", ShortDate)] - where can I do this?

 

Usage_Trend is fine - it is working.

UsageCollect - not working yet..

Error - invalid argument type.

 

ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Modified", "Grouped"), "Nums", CountRows(Grouped)), "Modified", Descending))

 

Meneghino
Level 10

Re: Sort by date is not working - please help

You are right.  Try this please:

/*First Table:*/
ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values of two columns only into a master table*/
 
/*Second Table:*/
ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(
	AddColumns(UsageTrend, "MyDate", Date(Year(Modified), Month(Modified), Day(Modified))),
	"MyDate", "Grouped"), "Nums", CountRows(Grouped)), "Modified", Descending))

 

You can format the date as text when you have to display it, like this:

Text(MyDate, "mm/dd/yyyy")

 

View solution in original post

jamesantony
Level: Powered On

Re: Sort by date is not working - please help

So kind of you @Meneghino

 

Great responses. I updated like this and it worked like charm. It was actually the text format that caused the issue. If I put Date format with long date, short date etc., it is not working. With this format"mm/dd/yyyy" it worked like a charm.

 

is there a way to get the data labels in the graph? alos is it possible to change the line color and thickness?

I see the item colorset but there is a lot... not sure whichone represents the line.

 

 

 

/*Colelction for Usage Trend Graph*/

ClearCollect(UsageTrend, ShowColumns('Lessons Learned Library', "Modified", "Test_Field")); /*Get the values into a master Table*/
ClearCollect(UsageTrend, AddColumns('Lessons Learned Library', "Usage_Date", Text(Modified, "[$-en-US]mm/dd/yyyy"))); /*Take only the date*/

ClearCollect(UsageCollect, SortByColumns(AddColumns(GroupBy(UsageTrend, "Usage_Date", "Grouped"), "Nums", CountRows(Grouped)),"Usage_Date", Ascending))

 

View solution in original post

Meneghino
Level 10

Re: Sort by date is not working - please help

Glad to help.  Have not worked with graphs for a long time so can't assist you on that one.

jamesantony
Level: Powered On

Re: Sort by date is not working - please help

no worries. Thank you.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 342 members 5,726 guests
Please welcome our newest community members: