How To Split This Tables into Rows

[MedExcels]

Hello,
Is there any solution to convert this table with powerquery into a spécific format.

The table and the specific format are in the excel workbook joined here in this link
http://www.cjoint.com/c/GJlkrSPIVzE


Thanksssssssss

 

[Special-K99]

The output table is exactly the same as the input table (only right justified).

It does not match your thread description of "Split this table into rows"

Check the file you've posted.

 

[MedExcels]

The output table is exactly the same as the input table (only right justified).

It does not match your thread description of "Split this table into rows"

Check the file you've posted.

The input table is not the output table

In the input Table, Data is stocked in one cell "A2", I want to split it to make table for every value in a distinct row

 

[Special-K99]

Ah yes, I see now.

 

[Special-K99]

in G2

=TRIM(MID(SUBSTITUTE(A$2,CHAR(10),REPT(" ",LEN(A$2))),((ROW()-1)*(LEN(A$2))-LEN(A$2))+1,LEN(A$2)))

copy across and down as far as K11

 

[MedExcels]

in G2

=TRIM(MID(SUBSTITUTE(A$2,CHAR(10),REPT(" ",LEN(A$2))),((ROW()-1)*(LEN(A$2))-LEN(A$2))+1,LEN(A$2)))

copy across and down as far as K11

Thanks a lottttttttttt for the formulas it is very handy

But is there a solution with powequery?

 

[Special-K99]

Afraid I'm not familiar with Powerquery.

 

[MedExcels]

Afraid I'm not familiar with Powerquery.

Thanksssssssssssss

 

[MarcelBeug]

This would be a Power Query solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    // 2 intermediate steps to store column names and (new) column tyoes:
    Columns = Table.ColumnNames(Source),
    ColumnTypes = List.Transform(Columns, each {_, Int64.Type}),

    LinesToText = Table.TransformColumns(Source,{},Lines.FromText),
    TableFromColumns = Table.AddColumn(LinesToText, "Custom", each Table.FromColumns(Record.FieldValues(_),Columns)),
    RemovedColumns = Table.RemoveColumns(TableFromColumns,Columns),
    ExpandedTables = Table.ExpandTableColumn(RemovedColumns, "Custom", Columns),
    Typed = Table.TransformColumnTypes(ExpandedTables,ColumnTypes)
in
    Typed

 

[MarcelBeug]

OK MarcelBeug, of course you can use any step name, but "LinesToText" is not very appropriate for a "Lines.FromText" operation...