Transposing a Range (Multiple Columns and Rows) to a Single Row - new row based on cell value from one column

[alscno]

Hi,
I am trying to transpose multiple data from multiple columns into a single row in a new workbook. Every new row should contain the data from range "TX" to new "TX" in column 1.
My original data simplifies to what is seen below:
Col1 Col2
TX 1
A 2
B 3
TX 5
C 6
D 7
E 8

Needed output:
row1: TX 1 A 2 B 3
row 2: TX 5 C 6 D 7 E 8

Thanks!

 

[sandy666]

Column1	Column2		Custom.1
TX	1		TX 1 A 2 B 3
A	2		TX 5 C 6 D 7 E 8
B	3
TX	5
C	6
D	7
E	8

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TCC = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column2", type text}}, "en-GB"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Contains = Table.AddColumn(TCC, "Custom", each if Text.Contains([Merged], "TX") then [Merged] else null),
    FillD = Table.FillDown(Contains,{"Custom"}),
    Group = Table.Group(FillD, {"Custom"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Custom.1", each [Count][Merged]),
    Extract = Table.TransformColumns(List, {"Custom.1", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    TSC = Table.SelectColumns(Extract,{"Custom.1"})
in
    TSC

 

[alscno]

Thank you sandy666 ! I described the needed output not correctly above. How can i change the code so that the output is placed in seperat columns and not merged into one cell?

Column1	Column2		Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8
TX	1		TX	1	A	2	B	3
A	2		TX	5	C	6	D	7	E	8
B	3
TX	5
C	6
D	7
E	8

 

[sandy666]

Column1	Column2		List.1	List.2	List.3	List.4	List.5	List.6	List.7	List.8
TX	1		TX	1	A	2	B	3
A	2		TX	5	C	6	D	7	E	8
B	3
TX	5
C	6
D	7
E	8

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TCC = Table.CombineColumns(Table.TransformColumnTypes(Source, {{"Column2", type text}}, "en-GB"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    Contains = Table.AddColumn(TCC, "Custom", each if Text.Contains([Merged], "TX") then [Merged] else null),
    FillD = Table.FillDown(Contains,{"Custom"}),
    Group = Table.Group(FillD, {"Custom"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each [Count][Merged]),
    Extract = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), " "), type text}),
    MaxCount = List.Max(Table.AddColumn(Extract, "SCount", each List.Count(Text.Split([List]," ")))[SCount]),
    Split = Table.SplitColumn(Extract, "List", Splitter.SplitTextByDelimiter(" "), MaxCount),
    TSC = Table.SelectColumns(Split,{"List.1", "List.2", "List.3", "List.4", "List.5", "List.6", "List.7", "List.8"})
in
    TSC

 

[sandy666]

Thanks but I prefer Like