Combining Data from multiple rows and columns

shellz

New member
Joined
May 26, 2016
Messages
10
Reaction score
0
Points
0
Excel Version(s)
2016
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:Bank1EFF DATE:1-Jan-02
PF/CT/BNAMETS4 Cinnamon Chews (Category: X, Brand: CANDY)STATUS:In Progress
PROD NAME:Cinnamon ChewsAPPROVERS:
PROJECT:Project 1
Step #1Step Type: MasterStep Name : TS4
Ingr IdIngr Name ObjSolutionMinMaxWeightTotal %
TS4_1Ingr1sub12.0000005.00000012.0000007.500000
TS4_2Ingr2sub15.0000008.00000015.00000010.760000
Total Step #2Step Type: MixStep Name : TS4_MIX
Ingr IdIngr Name ObjSolutionMinMaxWeightTotal %
TS4_5Ingr5sub28.00000028.00000032.00000010.846877
TS4_6Ingr6sub72.00000068.00000072.00000027.891968
Total Step #3Step Type: BlendStep Name : TS4_BLEND
Ingr IdIngr Name ObjSolutionMinMaxWeightTotal %
9888Ingr9ing0.8400000.8400000.8400000.514594
9999Ingr4ing95.41000058.449268
TS4_3Ingr3sub3.7500003.7500003.7500002.297293


Into this:
PFProd NameBNAMEStepIngr IdIngr NameMinMaxSolutionTotal %
TS4Cinnamon ChewsCANDYTS4TS4_1Ingr15.00000012.00000012.0000007.500000
TS4Cinnamon ChewsCANDYTS4TS4_2Ingr28.00000015.00000015.00000010.760000
TS4Cinnamon ChewsCANDYMIXTS4_5Ingr528.00000032.00000028.00000010.846877
TS4Cinnamon ChewsCANDYMIXTS4_6Ingr668.00000072.00000072.00000027.891968
TS4Cinnamon ChewsCANDYBLEND9888Ingr90.8400000.8400000.8400000.514594
TS4Cinnamon ChewsCANDYBLEND9999Ingr495.41000058.449268
TS4Cinnamon ChewsCANDYBLENDTS4_3Ingr33.7500003.7500003.7500002.297293
 
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"
 
Back
Top