Wishmaster89
Board Regular
- Joined
- Jan 10, 2022
- Messages
- 77
- Office Version
- 2021
- 2019
- 2016
- Platform
- Windows
- MacOS
Hi All
I've been playing around with sorting in excel and power query. Power query gives me the result i expect and excel doesn't and i can't figure out why.
Sample data
M query for the sort
Now with the same data in the excel sheet, if i replicate the sort in excel like in the attached image the data comes out like this(Column G value changes row). Any one able to tell me why please?
I've been playing around with sorting in excel and power query. Power query gives me the result i expect and excel doesn't and i can't figure out why.
Sample data
status to check for QAR comparison 18-09.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ULN | Completion_Status | Learning_Outcome | Standard_Description | Learning_Start_Date | Learning_Planned_End_Date | Learning_Actual_End_Date | ||
2 | 1 | 1 | 9 | Credit Controller / Collector | 16/08/2017 00:00 | 15/02/2019 00:00 | |||
3 | 1 | 2 | 1 | Credit Controller / Collector | 16/08/2017 00:00 | 15/02/2019 00:00 | |||
4 | 1 | 2 | 1 | Credit Controller / Collector | 16/08/2017 00:00 | 15/02/2019 00:00 | 20/06/2019 00:00 | ||
test_v4 |
M query for the sort
Power Query:
let
Source = Excel.CurrentWorkbook(){[Name="test_v4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ULN", Int64.Type}, {"Completion_Status", Int64.Type}, {"Learning_Outcome", Int64.Type}, {"Standard_Description", type text}, {"Learning_Start_Date", type datetime}, {"Learning_Planned_End_Date", type datetime}, {"Learning_Actual_End_Date", type datetime}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Learning_Start_Date", Order.Ascending}, {"Learning_Planned_End_Date", Order.Ascending}, {"Learning_Actual_End_Date", Order.Ascending}})
in
#"Sorted Rows"
Now with the same data in the excel sheet, if i replicate the sort in excel like in the attached image the data comes out like this(Column G value changes row). Any one able to tell me why please?
status to check for QAR comparison 18-09.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | ULN | Completion_Status | Learning_Outcome | Standard_Description | Learning_Start_Date | Learning_Planned_End_Date | Learning_Actual_End_Date | ||
2 | 1 | 2 | 1 | Credit Controller / Collector | 16/08/2017 00:00 | 15/02/2019 00:00 | 20/06/2019 00:00 | ||
3 | 1 | 1 | 9 | Credit Controller / Collector | 16/08/2017 00:00 | 15/02/2019 00:00 | |||
4 | 1 | 2 | 1 | Credit Controller / Collector | 16/08/2017 00:00 | 15/02/2019 00:00 | |||
test_v4 |