如何使用Google Dataprep按自定义顺序对Excel表Stage列排序？

No worries if this is a repeat question—happy to walk you through exactly how to get your custom sort order set up in Google Dataprep and push the final data to BigQuery.

Step 1: Create a Custom Sort Weight Column #

Since Dataprep doesn’t let you directly sort by a custom value order out of the box, we’ll first make a helper column that assigns a numeric weight to each "Stage" value matching your desired sequence.

• Open your Excel dataset in Google Dataprep.
• Click the + Add column button and select Add computed column.
• Name the new column something like Stage_Sort_Order, then paste this CASE statement into the formula editor (tweak the ELSE clause if you need to handle unlisted Stage values differently):

CASE
  WHEN Stage = 'Closed - Won' THEN 1
  WHEN Stage = 'Stage Three' THEN 2
  WHEN Stage = 'Stage Two' THEN 3
  WHEN Stage = 'Stage One' THEN 4
  WHEN Stage = 'Prospect' THEN 5
  WHEN Stage = 'Closed - Lost' THEN 6
  ELSE 999 -- Puts any unrecognized values at the end of the sorted list
END

• Click Add to save the column.

Step 2: Sort Your Data Using the Helper Column #

Now use the weight column to rearrange your dataset into your preferred order:

• Select your full dataset (or just the columns you need to keep).
• Click the Sort button in the top toolbar.
• In the sort panel:
  • Choose Stage_Sort_Order as the column to sort by.
  • Set the direction to Ascending (since our weights map directly to your desired sequence from first to last).
• If you don’t want the helper column in your final output, right-click the Stage_Sort_Order header and select Hide.

Step 3: Export the Sorted Data to BigQuery #

Once your data is sorted correctly, push it to BigQuery:

• Click the Run Job button in the top-right corner.
• In the output configuration screen:
  • Under Destination, select BigQuery.
  • Pick your Google Cloud project, target dataset, and enter a name for your sorted table.
  • Choose a write mode (e.g., Overwrite to replace an existing table, or Append to add new data to it).
• Adjust any extra settings like partitioning or clustering if needed, then click Run to start the export job.

Quick Tip #

If you have unexpected values in the "Stage" column, the ELSE 999 in the CASE statement will push them to the bottom. You can change this value (e.g., set to 0 to put unlisted values at the top) based on your needs.

内容的提问来源于stack exchange，提问作者Courtney Henson