CSV file Sorting Issues

[bskiran1711]

Hi,

I have  a CSV file from which

1. we create a new column "Sort" based on the Column "Loan Type". 

2. Data is sorted based on Column "TS_Number" and then on the Column "Sort". 

3. Duplicates are removed from Column "TS Number"

4. Data is selected for Current year.

I see the results dont match to the manual Excel Calculation.

let
Source = Csv.Document(File.Contents("C:\Users\Bangalore.Kiran\OneDrive - Archwell Holdings LLC\Nestor_Pipeline\current_holds.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MasterId", Int64.Type}, {"TSNumber", type text}, {"StateCode", type text}, {"HoldReason", type text}, {"StartDate", type datetime}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Sort", each if [HoldReason] = "Bankruptcy" then 1 else 2),
#"Sorted Rows" = Table.Sort(#"Added Conditional Column",{{"TSNumber", Order.Ascending}, {"Sort", Order.Ascending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"TSNumber"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each Date.IsInCurrentYear([StartDate]))
in
#"Filtered Rows"

[Anonymous]

Hi @bskiran1711 ,

Thanks for reaching out to our community.

Pls has your problem been solved? If so, accept the reply as a solution. This will make it easier for the future people to find the answer quickly.

If not, please provide a more detailed description.

Best Regards,

Stephen Tao

[PwerQueryKees]

Table.Distinct does not have a garantueed sort order as result. So do the distinct first and then the sort.

[Omid_Motamedise]

in the second step, ( Data is sorted based on Column "TS_Number" and then on the Column "Sort". ) when you sort the table, a functin is generated like 

List.Sort(.............) add Table.Buffer into it and rewrite it as Table.Buffer(List.Sort(.............)).

it make power query to sort the data first and then continue other steps, otherwise, power query will reorder the steps and apply the sort on the last result.

[ZhangKun]

add Table.Buffer function:

let
Source = Csv.Document(File.Contents("C:\Users\Bangalore.Kiran\OneDrive - Archwell Holdings LLC\Nestor_Pipeline\current_holds.csv"),[Delimiter=",", Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"MasterId", Int64.Type}, {"TSNumber", type text}, {"StateCode", type text}, {"HoldReason", type text}, {"StartDate", type datetime}}),
#"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Sort", each if [HoldReason] = "Bankruptcy" then 1 else 2),
#"Sorted Rows" = Table.Buffer(Table.Sort(#"Added Conditional Column",{{"TSNumber", Order.Ascending}, {"Sort", Order.Ascending}})),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"TSNumber"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each Date.IsInCurrentYear([StartDate]))
in
#"Filtered Rows"

[bskiran1711]

Hi

Can you please let me know how to add table.buffer. Thanks in Advance

Regards

Kiran

[ZhangKun]

add it in #"Sorted Rows"（I have added it in the reply above）.

Or you also add it in #"Removed Duplicates", see following code:

#"Removed Duplicates" = Table.Distinct(Table.Buffer(#"Sorted Rows"), {"TSNumber"}),