如何在Power Query中逆透视多列实现数据结构转换？

Got it, this is a classic unpivot scenario (you were right to think it's pivot-related—just the reverse!) where we need to turn your wide table into a long, structured format by pairing each date with its corresponding work days and revenue. Here's a step-by-step guide that works in all versions of Power Query:

Step 1: Load Your Data into Power Query #

First, get your table into Power Query:

• Go to the Data tab in Excel (or Home tab in Power BI)
• Click Get Data → select From Table/Range (make sure your data has headers)
• Power Query Editor will open with your raw table.

Step 2: Fix Duplicate Revenue Column Names #

Power Query automatically renames duplicate columns, so your two Revenue columns will show up as Revenue and Revenue.1. Let's rename them to link directly to their matching dates:

• Right-click the first Revenue column (matches the 01/01/2023 work days) → Rename → type Revenue_01/01/2023
• Right-click Revenue.1 (matches 01/02/2023 work days) → Rename → type Revenue_01/02/2023

Step 3: Unpivot All Non-Name Columns #

We need to flatten the wide columns into rows first:

• Select the Name column (click its header to highlight it)
• Go to the Transform tab → click the dropdown under Unpivot Columns → choose Unpivot Other Columns
• You’ll now have three columns: Name, Attribute, Value (each row represents a single data point for a name)

Step 4: Add a Custom Column to Split Date and Data Type #

Next, we’ll extract the date and whether the value is work days or revenue from the Attribute column:

• Go to the Add Column tab → click Custom Column
• Paste this formula into the dialog box (it checks if the attribute is a revenue column, then extracts the date and type):

  let
      attr = [Attribute],
      isRevenue = Text.StartsWith(attr, "Revenue_"),
      Date = if isRevenue then Text.AfterDelimiter(attr, "_") else attr,
      Type = if isRevenue then "Revenue" else "Work Days"
  in
      [Date=Date, Type=Type]
  

• Click OK — you’ll see a new column with records containing Date and Type

Step 5: Expand the Custom Column #

Let’s turn the record values into separate columns:

• Click the expand icon (⊕) on the header of your new custom column
• Check both Date and Type in the popup → click OK

Step 6: Pivot to Combine Work Days and Revenue #

Now we’ll turn the Type values into dedicated columns:

• Select the Type column
• Go to the Transform tab → click Pivot Column
• In the dialog:
  • Choose Value as the Values Column
  • Uncheck Aggregate Value Function (since each name/date pair has exactly one value per type)
• Click OK

Step 7: Clean Up the Final Table #

• Right-click the original Attribute column → select Remove to delete it
• Reorder your columns to match your desired structure: drag Name first, then Date, Work Days, Revenue

That’s it! You’ll have the exact long-format table you wanted, with each row representing a name-date combination with its corresponding work days and revenue.

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