Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
YunJ
Post Prodigy
Post Prodigy

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
v-xuding-msft
Community Support
Community Support

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
v-xuding-msft
Community Support
Community Support

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.

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

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.

@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

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.

Thanks a lot @v-xuding-msft 

 

I found that you make "Date" as descending. But actually I want it to be ascending. But when I set ascending, the blank in the "Date" will be show at first... Is there any solution to place Date (with values) at first?

Capture.PNG

Thanks for your patience.

Hi @YunJ ,

 

The null numbers equal 0 in Power BI. So we can't set blank values in the end of columns and others are sorted by ascending for the situation of sorting multiple columns.

 

 

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.

Thanks @v-xuding-msft 

 

I followed your steps. But because I don't want to put "Index" into the table visual. So I tried to sort column. But failed..(there should be unique index for each row, why it still failed?)

Is there any solution to sort without putting "Index" into the table visual? 

Capture.PNG

Thanks for your time.

Hi @YunJ ,

 

Sorry for late back.

 

The error is caused by the blank values in the columns. If you don't want to use index column, you could create a formula using the RANKX function to have a try. However, it is a little complexed. You could reference the blog to have a try.

https://community.powerbi.com/t5/Community-Blog/Breaking-Ties-in-Rankings-with-RANKX-Using-Multiple-...

 

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.
amitchandak
Super User
Super User

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

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.