Formatting Excel Table

Question

I have transformed data (using Power Query) in Excel from PDF and it currently looks like this (where each space represents a new column and each letter represents a different cell's data):

Column1	Column2	Column3	Column4
Char1	A	B	C
Char1	D	E	F
Char1	G	H	I
Char1	J	K	L
Char2	A	B	C
Char2	D	E	F
Char2	G	H	I
Char2	J	K	L
Char3	A	B	C
Char3	D	E	F
Char3	G	H	I
Char3	J	K	L

but I would like it to look like this:

Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Column11	Column12	Column13
Char1	A	B	C	D	E	F	G	H	I	J	K	L
Char2	A	B	C	D	E	F	G	H	I	J	K	L
Char3	A	B	C	D	E	F	G	H	I	J	K	L

Any tips on how to do this?

Answer 1

You can try this

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source , {"Column1"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Column1"}, {{"data", each  Text.Combine(_[Value],","), type text}}),
Columns = List.Transform({2 .. 1+List.Max(Table.AddColumn(#"Grouped Rows", "count", each List.Count(Text.PositionOfAny([data],{","},Occurrence.All))+1)[count])}, each "Column " & Text.From(_)),
#"Split Column by Delimiter" = Table.SplitColumn(#"Grouped Rows", "data", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), Columns)
in #"Split Column by Delimiter"