如何用Power Automate自动化VLOOKUP数据清洗及格式转换？

方案1：Python脚本+文件夹监控（高度自定义，无格式限制） #

步骤： #

1. 准备映射表：将CRM与EMS的产品对应关系保存为product_mapping.csv，格式如下：

   Product CRM name,Product EMS name
   Car (Red),Red Car
   Bike (Blue),Blue Bike
   Horse (Orange),Orange Horse
   

2. 编写处理脚本：用pandas库实现数据清洗，示例代码：

   import pandas as pd
   import os
   
   # 配置路径
   UPLOAD_FOLDER = "C:/YourUploadFolder"
   OUTPUT_FOLDER = "C:/YourOutputFolder"
   MAPPING_FILE = "C:/YourMappingFolder/product_mapping.csv"
   
   # 加载映射字典
   mapping_df = pd.read_csv(MAPPING_FILE)
   product_map = dict(zip(mapping_df['Product CRM name'], mapping_df['Product EMS name']))
   
   # 获取上传文件夹的最新文件
   files = [f for f in os.listdir(UPLOAD_FOLDER) if f.endswith(('.csv', '.xlsx'))]
   if not files:
       exit()
   latest_file = max(files, key=lambda x: os.path.getctime(os.path.join(UPLOAD_FOLDER, x)))
   file_path = os.path.join(UPLOAD_FOLDER, latest_file)
   
   # 读取联系人文件
   if latest_file.endswith('.csv'):
       df = pd.read_csv(file_path)
   else:
       df = pd.read_excel(file_path)
   
   # 替换产品名称并重命名列
   for slot in ['ProductSlot1', 'ProductSlot2', 'ProductSlot3']:
       if slot in df.columns:
           df[slot] = df[slot].map(product_map).fillna('')
           df.rename(columns={slot: slot.replace('Product', 'Dynamic')}, inplace=True)
   
   # 输出清洗后的CSV
   output_file = f"Cleaned_{latest_file.split('.')[0]}.csv"
   df.to_csv(os.path.join(OUTPUT_FOLDER, output_file), index=False, encoding='utf-8')
   

3. 设置自动触发：
   • 用watchdog库监听文件夹，有新文件时自动执行脚本；
   • 或用Windows任务计划程序，定时扫描上传文件夹，发现新文件就运行脚本。

优势： #

• 支持任意命名的文件，解决“固定文件限制”问题；
• 自定义程度高，可灵活调整清洗规则；
• 输出CSV直接符合EMS导入要求，无需额外处理。

方案2：改进Power Query自动化流程（无代码，适配Excel生态） #

步骤： #

1. 配置文件夹数据源：
   • 在Excel中打开Power Query，选择数据>获取数据>从文件>从文件夹，指定上传文件夹；
   • 添加筛选条件，只保留最新创建的文件（按“创建时间”降序，取第一行）。
2. 加载映射表：
   • 导入产品映射表，创建名为ProductMapping的查询，将其转换为字典格式（转换>转置>第一行作为标题，再用管理数据>创建字典）。
3. 批量替换与列重命名：
   • 对联系人表的ProductSlot1/2/3列，使用替换值功能，引用ProductMapping字典进行匹配；
   • 重命名列：将ProductSlotX改为DynamicSlotX。
4. 自动刷新与导出：
   • 设置数据刷新计划（数据>刷新全部>连接属性>刷新频率）；
   • 编写简单VBA宏，在刷新完成后自动将结果保存为CSV到目标文件夹，示例宏代码：

     Sub ExportToCSV()
         ActiveWorkbook.Sheets("清洗后数据").Copy
         ActiveWorkbook.SaveAs Filename:="C:/YourOutputFolder/Cleaned_Contacts.csv", FileFormat:=xlCSVUTF8
         ActiveWorkbook.Close SaveChanges:=False
     End Sub
     

   • 将宏与Power Query的刷新事件绑定，或用Windows任务计划定时触发刷新+导出。

优势： #

• 无需编程，适合Excel熟悉用户；
• 自动识别文件夹内新文件，解决固定文件限制；
• 输出CSV直接可用，无需额外清洗。

方案3：改进Power Automate云端流程（适配企业云环境） #

步骤： #

1. 设置触发条件：选择当文件创建时（OneDrive/SharePoint），指定上传文件夹。
2. 加载映射表：在云端存储（如OneDrive）存放产品映射表，用获取文件内容动作读取。
3. 数据转换：
   • 用Excel Online（商务）>列出表行读取联系人文件的内容；
   • 用应用到每一项遍历所有行，对每个ProductSlotX列使用查找匹配项动作，从映射表中获取EMS名称；
   • 重命名列，构建新的表格行。
4. 输出CSV：用创建CSV表动作生成清洗后的内容，再用创建文件动作保存到目标文件夹。

注意点： #

• 提前在联系人文件和映射表中创建Excel表（不是普通单元格区域），避免Power Automate读取数据异常；
• 若映射表条目多，可先将其转换为JSON格式，用解析JSON动作提高匹配效率。

内容的提问来源于stack exchange，提问作者Ihavenomouth yetimusttest