一个简单的方法

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Set0=List.FirstN(Table.ColumnNames(Source),1),
Set1= List.Combine({Set0,List.Alternate(Table.ColumnNames(Source),1,1)}),
Set2=List.Combine({Set0,List.Alternate(List.RemoveFirstN(Table.ColumnNames(Source),1),1,1)}),
Part1 = Table.SelectColumns(Source,Set1),
Part2 = Table.SelectColumns(Source,Set2),
Date1 = Table.AddColumn(Part1,"Date" , each Table.ColumnNames(Part1){1}),
Date2 = Table.AddColumn(Part2,"Date" , each Table.ColumnNames(Part2){1}),
Rename1 = Table.RenameColumns(Date1,{{Table.ColumnNames(Part1){2}, "Revenue"}, {Table.ColumnNames(Part1){1}, "Work Days"}}),
Rename2 = Table.RenameColumns(Date2,{{Table.ColumnNames(Part2){2}, "Revenue"}, {Table.ColumnNames(Part2){1}, "Work Days"}}),
combined = Rename1 & Rename2
in combined

或

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index", "Name"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Date", each if Text.Start([Attribute],3)="Rev" then null else [Attribute]),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "count", each if Text.Start([Attribute],3)="Rev" then null else [Value]),
#"Added Index1" = Table.AddIndexColumn(#"Added Custom2", "Index.1", 0, 1, Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Added Index1", "Modulo", each Number.Mod([Index.1], 2), type number),
#"Sorted Rows" = Table.Sort(#"Inserted Modulo",{{"Index", Order.Ascending}, {"Modulo", Order.Ascending}, {"Attribute", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Date", "count"}),
x=Table.AlternateRows(#"Filled Down",0,1,1),
#"Removed Other Columns" = Table.SelectColumns(x,{"Name", "Value", "Date", "count"})
in  #"Removed Other Columns"

这里有另一种方法：

• 使用List.Generate，使用每个Date/Revenue对创建一个List of Tables。
• 对于每个表，确保Revenue列命名为Revenue（而不是Revenue 2、Revenu 3等），然后Unpivot该表。
• 然后展开包含表列表的列
• 剩下的就是“看家”了
  已编辑代码，以便在日期和收入列集合之前保留不同数量的“第一列”
• 更改**#“Retained Column Count”**以反映该列数

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    colNames = List.Buffer(Table.ColumnNames(Source)),

//How many columns at the beginning are non data pairs
    #"Retained Column Count" = 4,

    #"First Columns" = List.FirstN(colNames,#"Retained Column Count"),
    #"Date Columns" = List.Range(colNames,#"Retained Column Count",(List.Count(colNames)-#"Retained Column Count")/2),
    #"Revenue Columns" = List.LastN(colNames,List.Count(#"Date Columns")),

//set data types
    types = List.Transform(#"First Columns", each {_, type text}) &
            List.Transform(#"Date Columns", each {_, Int64.Type}) &
            List.Transform(#"Revenue Columns", each {_, Currency.Type}),
    #"Changed Type" = Table.TransformColumnTypes(Source, types, "en-GB"),

//create a list of tables consisting of each date/revenue pair
// then unpivot each table
// ensure Revenue column has the same name throughout
    #"Data Pairs" = List.Generate(
        ()=>[t=Table.SelectColumns(#"Changed Type",#"First Columns" & {#"Date Columns"{0}} & {#"Revenue Columns"{0}}), idx=0],
            each [idx] < List.Count(#"Date Columns"),
            each [t=Table.SelectColumns(#"Changed Type",#"First Columns" & {#"Date Columns"{[idx]+1}} & {#"Revenue Columns"{[idx]+1}}), idx=[idx]+1],
            each Table.Unpivot(
                    Table.RenameColumns([t], {Table.ColumnNames([t]){#"Retained Column Count"+1},"Revenue"}),
                        {#"Date Columns"{[idx]}},"Date","Work Days")),

//to a table
//  then combine the tables with column names in desired order
    #"Converted to Table" = Table.FromList(#"Data Pairs", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", #"First Columns" & {"Date","Work Days","Revenue"}),
  
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded Column1", 
        List.Transform(#"First Columns", each {_, type text})  &
        {{"Date", type date},
        {"Work Days", Int64.Type},
        {"Revenue", Currency.Type}}, "en-GB"),
        
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"