-1

I try to convert/transform excel query to google sheets.

This the code from excel:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type any}, {"Column15", type any}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Test1№", Int64.Type}, {"test2", type any}, {"test3", type datetime}, {"test4", type text}, {"test5", type text}, {"test6", type text}, {"test7", type datetime}, {"test8", Int64.Type}, {"test9", type number}, {"test10", type text}, {"test11", type text}, {"test12", type text}, {"test13", type text}, {"Column14", type any}, {"Column15", type any}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"test1", Order.Ascending}, {"test3", Order.Descending}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Sorted Rows",{{"test3", type date}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type2", {"test1"})
in
    #"Removed Duplicates"

This the link: https://docs.google.com/spreadsheets/d/1fAuw7SkGjs6ZToWYWjB73c90U4lhOHSbyMeFwA7wMPk/edit?usp=sharing

I need to: Col Date need to be sorted last date In Test1 remove the duplicates Copy data in the sheet 2

Thanks in advance.

svetlinsem
  • 37
  • 5
  • 1
    share your spreadsheet and explain what do you expect – Mike Steelson Jan 22 '22 at 19:15
  • 1
    Yes, rather than showing us a formula that doesn't work, just share the Google Sheet data and manually enter the results that you'd like this formula to produce. Then edit your above post to explain as clearly as you can what transformation those results show to the original data. For instance, "I would like to see only entries where Column B has `[this word]` without any duplicates sorted by `[this column]` first and `[this column]` next." – Erik Tyler Jan 23 '22 at 13:23

1 Answers1

0

I found the solution with the following formula:

=SORTN(SORT(Sheet1!A2:T;3;false;1;true);10000;2;1;true)
Saman Salehi
  • 1,004
  • 1
  • 12
  • 19
svetlinsem
  • 37
  • 5