cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
Post Partisan

How to sort based on specific rule?

Hi,

I have a table like following. Some "Count Label" are blank, some "Date" are blank. 

Capture.PNGCapture2.PNG

I would like to sort the table by the following rules:

First: Both Count Label and Date have values.

Then: Count Label has values and Date is blank.

Then: Both Count Label and Date are blank.

webwxgetmsgimg.jfif

I tried to write measure like this, and I want to sort "Date" column by this, but failed...

Capture.PNGCapture2.PNG

Thanks a lot for your help!

Yun

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to sort based on specific rule?

Hi @YunJ ,

 

You could sort multiple columns in Query Editor and add an index column to sort them in report view. I copy the table of ROI Analysis-pbi and create a new sample. Please have a try to check if it works for you.

= Table.Sort(#"Reordered Columns",{{"Count Label", Order.Descending}, {"Date", Order.Descending}})
let
    Source = Excel.Workbook(File.Contents("C:\Users\xueding\Desktop\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type text}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type text}, {"Column25", type text}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", Int64.Type}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Count Label", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Version", type text}, {"Platform", type text}, {"Brand", type text}, {"Store", type text}, {"Category", type text}, {"Product", type text}, {"Type", type text}, {"GMV_af return", Int64.Type}, {"LS Sales Quantity_af return", Int64.Type}, {"LS Additional Mini in CIV", Int64.Type}, {"LS Commission", Int64.Type}, {"LS Entrance Fee", Int64.Type}, {"KOL", type text}, {"YearMonth_ROI", Int64.Type}, {"Date", Int64.Type}, {"RSP*", Int64.Type}, {"PSP*", Int64.Type}, {"Store Coupon*", Int64.Type}, {"Tmall Coupon*", type number}, {"On-page PSP*", Int64.Type}, {"Normal Mini*", type text}, {"LS Coupon*", Int64.Type}, {"LS PSP*", Int64.Type}, {"LS Mini*", type text}, {"Actual PSP*", type text}, {"Column", type number}, {"Mini in PV_on page*", Int64.Type}, {"Mini in PV_LS*", Int64.Type}, {"5", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Count Label", "Date", "Year", "Month", "Version", "Platform", "Brand", "Store", "Category", "Product", "Type", "GMV_af return", "LS Sales Quantity_af return", "LS Additional Mini in CIV", "LS Commission", "LS Entrance Fee", "KOL", "YearMonth_ROI", "RSP*", "PSP*", "Store Coupon*", "Tmall Coupon*", "On-page PSP*", "Normal Mini*", "LS Coupon*", "LS PSP*", "LS Mini*", "Actual PSP*", "Column", "Mini in PV_on page*", "Mini in PV_LS*", "5"})
    ,#"Sort Columns"= Table.Sort(#"Reordered Columns",{{"Count Label", Order.Descending}, {"Date", Order.Descending}})

in
    #"Sort Columns"

1.gif

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
Highlighted
Super User IV
Super User IV

Re: How to sort based on specific rule?

@YunJ , make sure you sort also have the same uniqueness as your original column. Then it will work.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Partisan
Post Partisan

Re: How to sort based on specific rule?

Hi @amitchandak 

 

The original column is also not unique. For example, I have multiple same date, but I also don't mind the sort with the same date.

Capture.PNG

Highlighted
Super User IV
Super User IV

Re: How to sort based on specific rule?

@YunJ , if ABC is value it can have value 1 everywhere for sort



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Post Partisan
Post Partisan

Re: How to sort based on specific rule?

Hi @amitchandak  Could you explain more...? I don't quite get what you said... Thanks for your patience.

Highlighted
Community Support
Community Support

Re: How to sort based on specific rule?

Hi @YunJ ,

 

You could sort multiple columns in Query Editor and add an index column to sort them in report view. I copy the table of ROI Analysis-pbi and create a new sample. Please have a try to check if it works for you.

= Table.Sort(#"Reordered Columns",{{"Count Label", Order.Descending}, {"Date", Order.Descending}})
let
    Source = Excel.Workbook(File.Contents("C:\Users\xueding\Desktop\Book1.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type text}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type text}, {"Column22", type text}, {"Column23", type any}, {"Column24", type text}, {"Column25", type text}, {"Column26", type any}, {"Column27", type text}, {"Column28", type text}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", Int64.Type}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Count Label", type text}, {"Year", Int64.Type}, {"Month", Int64.Type}, {"Version", type text}, {"Platform", type text}, {"Brand", type text}, {"Store", type text}, {"Category", type text}, {"Product", type text}, {"Type", type text}, {"GMV_af return", Int64.Type}, {"LS Sales Quantity_af return", Int64.Type}, {"LS Additional Mini in CIV", Int64.Type}, {"LS Commission", Int64.Type}, {"LS Entrance Fee", Int64.Type}, {"KOL", type text}, {"YearMonth_ROI", Int64.Type}, {"Date", Int64.Type}, {"RSP*", Int64.Type}, {"PSP*", Int64.Type}, {"Store Coupon*", Int64.Type}, {"Tmall Coupon*", type number}, {"On-page PSP*", Int64.Type}, {"Normal Mini*", type text}, {"LS Coupon*", Int64.Type}, {"LS PSP*", Int64.Type}, {"LS Mini*", type text}, {"Actual PSP*", type text}, {"Column", type number}, {"Mini in PV_on page*", Int64.Type}, {"Mini in PV_LS*", Int64.Type}, {"5", Int64.Type}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Count Label", "Date", "Year", "Month", "Version", "Platform", "Brand", "Store", "Category", "Product", "Type", "GMV_af return", "LS Sales Quantity_af return", "LS Additional Mini in CIV", "LS Commission", "LS Entrance Fee", "KOL", "YearMonth_ROI", "RSP*", "PSP*", "Store Coupon*", "Tmall Coupon*", "On-page PSP*", "Normal Mini*", "LS Coupon*", "LS PSP*", "LS Mini*", "Actual PSP*", "Column", "Mini in PV_on page*", "Mini in PV_LS*", "5"})
    ,#"Sort Columns"= Table.Sort(#"Reordered Columns",{{"Count Label", Order.Descending}, {"Date", Order.Descending}})

in
    #"Sort Columns"

1.gif

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Post Partisan
Post Partisan

Re: How to sort based on specific rule?

Hi @v-xuding-msft 

 

Thanks a lot for your help. But I found I don't have add index button.. May I know where I can find it?Capture.PNG

Highlighted
Community Support
Community Support

Re: How to sort based on specific rule?

Hi @YunJ ,

 

It is in Query Editor.  Please see the .gif above for more details.

 

Home > Transform data (in report view)> Add Column (in Query Editor) > Index column

2.PNG

1.PNG

 

Sorry for my mistake, I should attach the files in the first post. Please download them to have a try.

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Post Partisan
Post Partisan

Re: How to sort based on specific rule?

@v-xuding-msft Thanks a lot for your reply!

May I ask why you write "Reordered Columns" (seems reduntant to list all the columns name again)? Is it the necessary step in order to write "Sort Columns"? or it has other functions?

Capture.PNG

Highlighted
Community Support
Community Support

Re: How to sort based on specific rule?

Hi @YunJ ,

 

No, it is not necessary. For the step of reordering the column, I drag the date column beside Count Label column. it is convenient to show the results of sorting those two columns. You could delete the step directly in the pane of applied steps.

4.PNG

 

Sorry for my mistake, I should attach the files in the first post. Please download them below to have a try.

 

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors