Combining Data from multiple rows and columns

[shellz]

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

 

[Nick Burns]

Going through looking for past unanswered "challenges"
Done with the UI

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"