Excel Challenge 1038

ExcelChallenge1038.pq

let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Data", type text}}), 
  #"Split Column by Delimiter" = Table.SplitColumn(
    #"Changed Type", 
    "Data", 
    Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
    {"Data.1", "Data.2", "Data.3", "Data.4"}
  ), 
  #"Changed Type After Split" = Table.TransformColumnTypes(
    #"Split Column by Delimiter", 
    {{"Data.1", type text}, {"Data.2", type text}, {"Data.3", type text}, {"Data.4", type text}}
  ), 
  #"Select Columns" = Table.SelectColumns(#"Changed Type After Split", {"Data.2", "Data.4"}), 
  #"Lowercased Text" = Table.TransformColumns(
    #"Select Columns", 
    {{"Data.4", Text.Lower, type text}}
  ), 
  RemoveChars = Table.AddColumn(
    #"Lowercased Text", 
    "Prep", 
    each RemoveChars([Data.4], false, false, true, true, "kbmt"), 
    type text
  ), 
  InitialTx = Table.TransformColumns(
    RemoveChars, 
    {
      "Prep", 
      each 
        if Text.Start(_, 1) = "." then
          "0" & _
        else if List.Count(List.Intersect({{"a" .. "z"}, Text.ToList(_)})) > 1 then
          null
        else if Text.Contains(_, "t") then
          null
        else if Text.Contains(_, "-") then
          null
        else
          _
    }
  ), 
  FinalTx = Table.TransformColumns(
    InitialTx, 
    {
      "Prep", 
      each try
        
          if Text.Start(_, 1) = "k" then
            null
          else if Text.Contains(_, "k") then
            Number.From(Text.Replace(_, "k", "")) * 1000
          else if Text.Contains(_, "m") then
            Number.From(Text.Replace(_, "m", "")) * 1000000
          else if Text.Contains(_, "b") then
            Number.From(Text.Replace(_, "b", "")) * 1000000000
          else if Value.Is(Number.From(_), type number) then
            Number.From(_)
          else
            null
      otherwise
        null
    }
  ), 
  #"Changed Type After Tx" = Table.TransformColumnTypes(FinalTx, {{"Prep", type number}}), 
  #"Rounded Off" = Table.TransformColumns(
    #"Changed Type After Tx", 
    {{"Prep", each Number.RoundAwayFromZero(_, 0), type number}}
  ), 
  #"Renamed Columns" = Table.RenameColumns(
    #"Rounded Off", 
    {{"Prep", "Salary"}, {"Data.2", "Employee"}}
  ), 
  #"Select Answer" = Table.SelectColumns(#"Renamed Columns", {"Employee", "Salary"})
in
  #"Select Answer"