Forum Discussion

lxjen's avatar
lxjen
Regular Visitor
1 year ago
Solved

Transform Table With Years and Sales Targets

Good morning folks,

 

I have a data connection that outputs data that looks like this:

 

Business CaseTarget First Invoice YearY1 TargetY2 TargetY3 TargetY4 TargetY5 Target
BC00120251000025000350004000050000
BC002202625000500007500090000100000
BC003202750007500100001100012000

 

And I'd like to overlay it against sales data, which would require it to be in this form:

 

Business CaseYearSales
BC001202510000
BC001202625000
BC001202735000
BC001202840000
BC001202950000
BC002202625000
BC002202750000
BC002202875000
BC002202990000
BC0022030100000
BC00320275000
BC00320287500
BC003202910000
BC003203011000
BC003203112000

 

I haven't quite figured out how to achieve this yet. Can anyone assist?

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi lxjen ,

    Paste this M in your Advanced editor:

    let
    // Step 1: Load Excel file, replace "YourFilePath.xlsx" with your actual file path
    Source = Excel.Workbook(File.Contents("C:\Path\To\YourFile.xlsx"), null, true),
    
    // Step 2: Load the specific sheet, replace "YourSheetName" with the actual sheet name
    Sheet_Data = Source{[Item="YourSheetName", Kind="Sheet"]}[Data],

    // Step 3: Promote headers to use the first row as column names
    #"Promoted Headers" = Table.PromoteHeaders(Sheet_Data, [PromoteAllScalars=true]),

    // Step 4: Change data types to ensure proper formatting
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", 
        {{"Business Case", type text}, 
         {"Target First Invoice Year", Int64.Type}, 
         {"Y1 Target", Int64.Type}, 
         {"Y2 Target", Int64.Type}, 
         {"Y3 Target", Int64.Type}, 
         {"Y4 Target", Int64.Type}, 
         {"Y5 Target", Int64.Type}}),

    // Step 5: Unpivot Y1-Y5 Target columns to transform data into a long format
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"Business Case", "Target First Invoice Year"}, 
        "Year Offset", "Sales"),

    // Step 6: Extract the year offset number from the column name (e.g., "Y1 Target" → 1)
    #"Extracted Offset Number" = Table.AddColumn(#"Unpivoted Columns", "Year Offset Number", 
        each Number.From(Text.Middle([Year Offset], 1, 1)), 
        Int64.Type),

    // Step 7: Calculate the actual sales year based on the target first invoice year
    #"Calculated Year" = Table.AddColumn(#"Extracted Offset Number", "Year", 
        each [Target First Invoice Year] + [Year Offset Number] - 1, 
        Int64.Type),

    // Step 8: Remove unnecessary columns
    #"Removed Unnecessary Columns" = Table.RemoveColumns(#"Calculated Year", 
        {"Year Offset", "Year Offset Number", "Target First Invoice Year"}),

    // Step 9: Rename columns for better readability
    #"Renamed Columns" = Table.RenameColumns(#"Removed Unnecessary Columns", 
        {{"Sales", "Sales"}}),

    // Step 10: Reorder columns to maintain logical order
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Business Case", "Year", "Sales"})

in
    #"Reordered Columns"

     

    This will result on this:

     

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi lxjen ,

    Paste this M in your Advanced editor:

    let
    // Step 1: Load Excel file, replace "YourFilePath.xlsx" with your actual file path
    Source = Excel.Workbook(File.Contents("C:\Path\To\YourFile.xlsx"), null, true),
    
    // Step 2: Load the specific sheet, replace "YourSheetName" with the actual sheet name
    Sheet_Data = Source{[Item="YourSheetName", Kind="Sheet"]}[Data],

    // Step 3: Promote headers to use the first row as column names
    #"Promoted Headers" = Table.PromoteHeaders(Sheet_Data, [PromoteAllScalars=true]),

    // Step 4: Change data types to ensure proper formatting
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", 
        {{"Business Case", type text}, 
         {"Target First Invoice Year", Int64.Type}, 
         {"Y1 Target", Int64.Type}, 
         {"Y2 Target", Int64.Type}, 
         {"Y3 Target", Int64.Type}, 
         {"Y4 Target", Int64.Type}, 
         {"Y5 Target", Int64.Type}}),

    // Step 5: Unpivot Y1-Y5 Target columns to transform data into a long format
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", 
        {"Business Case", "Target First Invoice Year"}, 
        "Year Offset", "Sales"),

    // Step 6: Extract the year offset number from the column name (e.g., "Y1 Target" → 1)
    #"Extracted Offset Number" = Table.AddColumn(#"Unpivoted Columns", "Year Offset Number", 
        each Number.From(Text.Middle([Year Offset], 1, 1)), 
        Int64.Type),

    // Step 7: Calculate the actual sales year based on the target first invoice year
    #"Calculated Year" = Table.AddColumn(#"Extracted Offset Number", "Year", 
        each [Target First Invoice Year] + [Year Offset Number] - 1, 
        Int64.Type),

    // Step 8: Remove unnecessary columns
    #"Removed Unnecessary Columns" = Table.RemoveColumns(#"Calculated Year", 
        {"Year Offset", "Year Offset Number", "Target First Invoice Year"}),

    // Step 9: Rename columns for better readability
    #"Renamed Columns" = Table.RenameColumns(#"Removed Unnecessary Columns", 
        {{"Sales", "Sales"}}),

    // Step 10: Reorder columns to maintain logical order
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Business Case", "Year", "Sales"})

in
    #"Reordered Columns"

     

    This will result on this:

     

    • lxjen's avatar
      lxjen
      Regular Visitor
      1 year ago

      This worked perfectly, thank you!

  • wini_R's avatar
    wini_R
    Icon for Solution Supplier rankSolution Supplier
    1 year ago

    Hi lxjen,

    You can easily do this in Power Query by unpivoting selected columns. Just select Business Case and Year columns, right-click column header and select Unpivot other columns

     

    • lxjen's avatar
      lxjen
      Regular Visitor
      1 year ago

      I don't think that does it. I only have a single year value in the source data, and in the new data I need a row for each year (the single year value is the source, and then each year will be source +1, source +2, etc.) and then I need to use the target values (Target Y1, Target Y2, etc. would be just Sales).

      • wini_R's avatar
        wini_R
        Icon for Solution Supplier rankSolution Supplier
        1 year ago

        EDIT: My bad, I missed one specific requirement regarding Year column. That is final output:

         

        Code to test in Power Query advanced editor:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7BDQAhCAR74e0DUDR+tQxj/22cB5son8luGMJaNCazUCJltQPhM3+0YAYLenPuFKKGWB/BsNiQO7IfvmYOs8GA8DwgAqpr+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Business Case" = _t, #"Target First Invoice Year" = Int64.Type, #"Y1 Target" = Int64.Type, #"Y2 Target" = Int64.Type, #"Y3 Target" = Int64.Type, #"Y4 Target" = Int64.Type, #"Y5 Target" = Int64.Type]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Business Case", "Target First Invoice Year"}, "Attribute", "Sales"),
    #"Extracted Text Range" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Attribute", each Number.From(Text.Middle(_, 1, 1))-1, Int64.Type}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Text Range", {"Target First Invoice Year", "Attribute"}, each List.Sum(List.Transform(_, (i)=> Number.From(i))) ,"Year")
in
    #"Merged Columns"