EN VI

Excel - Sorting rows and leaving blanks in their position?

How to Excel - Sorting rows and leaving blanks in their position

I have the following table with two columns:

Date	Book Title
01.01.2022
	Title1
	Title2
03.01.2022 <- unsorted
02.01.2022
	Title3
02.01.2022
	Title4

The Date rows work as a sort of sub-header for the column Book Title, but as you can see some dates aren't in ascending order. The duplicates aren't an issue.

My goal is to achieve the following (note the unchanged blank cells in Date):

Date	Book Title (remains unchanged)
01.01.2022
	Title1
	Title2
02.01.2022 <- fixed sorting
02.01.2022
	Title3
03.01.2022 <- fixed sorting
	Title4

However, what happens when I sort by ascend normally is that all blank cells are collected at the top of the table. I want to retain the blank cells where and as they are.

I managed to do this directly on Excel by hiding the blanks in Dates, sorting the remaining cells with the dates, and then unhiding the blank cells. This let me keep the blank cells as they are, and sort the dates only within the cells that are already populated.

How could I achieve this on Power Query instead?

Solution:

Try this in powequery

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Date", "Date - Copy"),
#"Filled Down" = Table.FillDown(#"Duplicated Column",{"Date - Copy"}),
#"Added Index" = Table.AddIndexColumn(#"Filled Down", "Index", 0, 1, Int64.Type),
#"Sorted Rows" = Table.Sort(#"Added Index",{{"Date - Copy", Order.Ascending}, {"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Date - Copy", "Index"})
in   #"Removed Columns"

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

#"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Book Title"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Date] <> null)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date", Order.Ascending}}),
Resort = {Table.ToColumns(#"Sorted Rows"){0}} & {Table.ToColumns(#"Filtered Rows"){1}},
Resorted=Table.FromColumns(Resort,{"Date","Index"}),

#"Removed Columns2" = Table.RemoveColumns(#"Added Index",{"Date"}),
#"Filtered Rows2" = Table.SelectRows(#"Removed Columns2", each ([Book Title] <> null)),

Combined =  Resorted & #"Filtered Rows2",
#"Sorted Rows1" = Table.Sort(Combined,{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows1",{"Index"})

in #"Removed Columns1"