break same number lines of column into multiple rows

[jppuam]

Hello,

i have a 1 column csv file, with data that repeats every 8 rows.

And i want to transpose that 8 rows of the column that i've into multiple rows, like in the example that is on image. How can i do this ?

thanks,

JR

[DatawithDinesh · Accepted solution]

You can use the Wraprows function in excel. This is as per you example. =WRAPROWS(A1:A24,8)
Select any cell and try the funtion.

Here is the syntax and lil bit of explation on what the function does.

WRAPROWS(vector, wrap_count, [pad_with]). 
Vector: The array or range of data you want to wrap into multiple rows
Wrap Count: The number of values to place in each row. This determines how many columns the output will have if wrapping rows.

Pad_with (optional): A value to fill in any empty cells if the total number of elements in the vector isn't evenly divisible by the wrap_count. If omitted, it defaults to #N/A.

[DatawithDinesh · Accepted solution]

Hello Jppuam,

Try this in Power BI. Sorry I wasn't able to figure out any straight forward way.

First import your data and make sure the use header as the first row option is selected. We don't want img to be your column name.
Then In Power Query Editor Choose Add Column > Index Column. 
In the modelling tab add New Columns

RowNumber = INT([Index] / 8 )+ 1

ColumnNumber = MOD([Index], 8 )+ 1

Once you have the columns added create a new table.

WrappedTable = 
SUMMARIZE(
    Sheet1,
    [RowNumber], 
    "Col1", MAXX(FILTER(Sheet1, [ColumnNumber] = 1 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col2", MAXX(FILTER(Sheet1, [ColumnNumber] = 2 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col3", MAXX(FILTER(Sheet1, [ColumnNumber] = 3 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col4", MAXX(FILTER(Sheet1, [ColumnNumber] = 4 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col5", MAXX(FILTER(Sheet1, [ColumnNumber] = 5 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col6", MAXX(FILTER(Sheet1, [ColumnNumber] = 6 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col7", MAXX(FILTER(Sheet1, [ColumnNumber] = 7 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1]),
    "Col8", MAXX(FILTER(Sheet1, [ColumnNumber] = 8 && [RowNumber] = EARLIER([RowNumber])), Sheet1[Column1])
)

Here is the step by step snapshot:

Hope this helps 🙂

[jppuam]

Thanks, it really helped. 😀

[DatawithDinesh · Accepted solution]

You can use the Wraprows function in excel. This is as per you example. =WRAPROWS(A1:A24,8)
Select any cell and try the funtion.

Here is the syntax and lil bit of explation on what the function does.

WRAPROWS(vector, wrap_count, [pad_with]). 
Vector: The array or range of data you want to wrap into multiple rows
Wrap Count: The number of values to place in each row. This determines how many columns the output will have if wrapping rows.

Pad_with (optional): A value to fill in any empty cells if the total number of elements in the vector isn't evenly divisible by the wrap_count. If omitted, it defaults to #N/A.

[jppuam]

Thanks DataWithDinesh,

it work more or less like i wanted. But its possible to do it on power bi ? because i've to do this with some frequence ?

JR