Results 1 to 2 of 2

Thread: Combining Data from multiple rows and columns

  1. #1
    Seeker shellz's Avatar
    Join Date
    May 2016
    Posts
    10
    Articles
    0
    Excel Version
    2016

    Combining Data from multiple rows and columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I would like to transform this table into the table below. I know how to get the PF, Name and Prod Name since they will always be in the same location. What I haven't figured out is how to get the Step Type and Step Name since they can be on varying rows within the sheet.

    LOCATION: Bank1 EFF DATE: 1-Jan-02
    PF/CT/BNAME TS4 Cinnamon Chews (Category: X, Brand: CANDY) STATUS: In Progress
    PROD NAME: Cinnamon Chews APPROVERS:
    PROJECT: Project 1
    Step #1 Step Type: Master Step Name : TS4
    Ingr Id Ingr Name Obj Solution Min Max Weight Total %
    TS4_1 Ingr1 sub 12.000000 5.000000 12.000000 7.500000
    TS4_2 Ingr2 sub 15.000000 8.000000 15.000000 10.760000
    Total Step #2 Step Type: Mix Step Name : TS4_MIX
    Ingr Id Ingr Name Obj Solution Min Max Weight Total %
    TS4_5 Ingr5 sub 28.000000 28.000000 32.000000 10.846877
    TS4_6 Ingr6 sub 72.000000 68.000000 72.000000 27.891968
    Total Step #3 Step Type: Blend Step Name : TS4_BLEND
    Ingr Id Ingr Name Obj Solution Min Max Weight Total %
    9888 Ingr9 ing 0.840000 0.840000 0.840000 0.514594
    9999 Ingr4 ing 95.410000 58.449268
    TS4_3 Ingr3 sub 3.750000 3.750000 3.750000 2.297293


    Into this:
    PF Prod Name BNAME Step Ingr Id Ingr Name Min Max Solution Total %
    TS4 Cinnamon Chews CANDY TS4 TS4_1 Ingr1 5.000000 12.000000 12.000000 7.500000
    TS4 Cinnamon Chews CANDY TS4 TS4_2 Ingr2 8.000000 15.000000 15.000000 10.760000
    TS4 Cinnamon Chews CANDY MIX TS4_5 Ingr5 28.000000 32.000000 28.000000 10.846877
    TS4 Cinnamon Chews CANDY MIX TS4_6 Ingr6 68.000000 72.000000 72.000000 27.891968
    TS4 Cinnamon Chews CANDY BLEND 9888 Ingr9 0.840000 0.840000 0.840000 0.514594
    TS4 Cinnamon Chews CANDY BLEND 9999 Ingr4 95.410000 58.449268
    TS4 Cinnamon Chews CANDY BLEND TS4_3 Ingr3 3.750000 3.750000 3.750000 2.297293

  2. #2
    Conjurer Nick Burns's Avatar
    Join Date
    May 2017
    Posts
    131
    Articles
    0
    Excel Version
    Office 365
    Going through looking for past unanswered "challenges"
    Done with the UI
    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type text}, {"Column9", type any}}),
        #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([Column1], "PF/") then [Column2] else null),
        #"Inserted Text Before Delimiter" = Table.AddColumn(#"Added Conditional Column", "PF", each Text.BeforeDelimiter([Custom], " "), type text),
        #"Inserted Text Between Delimiters" = Table.AddColumn(#"Inserted Text Before Delimiter", "BNAME", each Text.BetweenDelimiters([Custom], "Brand: ", ")"), type text),
        #"Added Conditional Column1" = Table.AddColumn(#"Inserted Text Between Delimiters", "Prod Name", each if Text.StartsWith([Column1], "PROD NAME") then [Column2] else null),
        #"Inserted Text After Delimiter" = Table.AddColumn(#"Added Conditional Column1", "Step", each Text.AfterDelimiter([Column4], " : "), type text),
        #"Added Conditional Column2" = Table.AddColumn(#"Inserted Text After Delimiter", "Ingr ID", each if [Column4] = "sub" then [Column1] else if [Column4] = "ing" then [Column1] else null),
        #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Ingr Name", each if [Column1] = [Ingr ID] then [Column2] else null),
        #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Min", each if [Column1] = [Ingr ID] then [Column6] else null),
        #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Max", each if [Column1] = [Ingr ID] then [Column7] else null),
        #"Added Conditional Column6" = Table.AddColumn(#"Added Conditional Column5", "Solution", each if [Column1] = [Ingr ID] then [Column5] else null),
        #"Added Conditional Column7" = Table.AddColumn(#"Added Conditional Column6", "Total %", each if [Column1] = [Ingr ID] then [Column9] else null),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column7",{"PF", "Prod Name", "BNAME", "Step", "Ingr ID", "Ingr Name", "Min", "Max", "Solution", "Total %"}),
        #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","",null,Replacer.ReplaceValue,{"PF", "Prod Name", "BNAME", "Step"}),
        #"Replaced Errors" = Table.ReplaceErrorValues(#"Replaced Value", {{"PF", null}, {"Prod Name", null}, {"BNAME", null}, {"Step", null}}),
        #"Filled Down" = Table.FillDown(#"Replaced Errors",{"PF", "Prod Name", "BNAME", "Step"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Ingr ID] <> null))
    in
        #"Filtered Rows"
    Oh... by the way, YOU'RE WELCOME!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •