I receive data that looks like this:

Where name is self-explanatory, the next two columns are dates (in UK format) with the number of days worked in the period shown below, and the final two columns are revenue.

I want to modify this data in Power Query so it looks like this:

I thought this would be some kind of a pivot operation but I can't figure it out.

Any assistance will be gratefully received.

Thanks,

Chris

CodePudding user response：

One simple way

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

or

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"

CodePudding user response：

Here's another way:

• Using List.Generate, create a List of Tables using each Date/Revenue Pair.
• For each of the tables, ensure the Revenue Column is named Revenue (and not Revenue2, Revenu3, etc) and then Unpivot the table.
• Then expand the column that has the list of tables
• The rest is "housekeeping"

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, 
        {"1/1/2023", Int64.Type}, {"1/2/2023", Int64.Type}, {"Revenue", type text}, {"Revenue2", type text}}),

    #"Data Columns" = List.RemoveFirstN(Table.ColumnNames(#"Changed Type"),1),

//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", {"Name",#"Data Columns"{0}, #"Data Columns"{0 List.Count(#"Data Columns")/2}}), idx=0],
            each [idx] < List.Count(#"Data Columns") / 2,
            each [t=Table.SelectColumns(#"Changed Type", {"Name",#"Data Columns"{[idx] 1}, #"Data Columns"{[idx] 1 List.Count(#"Data Columns")/2}}), idx=[idx] 1],
            each Table.Unpivot(
                    Table.RenameColumns([t], {Table.ColumnNames([t]){2},"Revenue"}),
                        {#"Data 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", {"Name", "Date", "Work Days", "Revenue"}),

//set the data types
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Expanded Column1", {
        {"Name", type text},
        {"Date", type date},
        {"Work Days", Int64.Type},
        {"Revenue", Currency.Type}}, "en-GB"),

//Sort to desired order
    #"Sorted Rows" = Table.Sort(#"Changed Type with Locale",{{"Name", Order.Ascending}, {"Date", Order.Ascending}})
in
    #"Sorted Rows"