Splitting out column data to show as 2 columns for a table.

[jmcp]

Hi

Is anyone able to help me understand how i can take the data in one column and split it into 2 columns?  i need to create a table that shows the 2 column headings.

My column has say, Data 1 and Data 2 as the text values  but they are in separate cells and i need to display them separately in 2 columns. 

Apologies if this seems simple but all i can find are references to delimiters which doesn't seem to work in this case.

[mwimberger]

Hi @jmcp 

If I understand your post correctly you need to split a column into two based on something that doesn't have delimiters, and then use the top row as the column headings. 

This is achievable a number of ways in Power Query which is simpler than doing it in DAX.

Split Column in the Transform tab of Power Query offers numerous ways to split a column including using delimiters or even number of characters.

If you need to use the first row of the data you imported as a header, use the "Use First Row as Headers" also under the transform tab under the Table group.

If it is something more complicated than this please post some more detail so we can assist.

Cheers

Manfred

[Anonymous]

Hi @jmcp It'll be helpful if you could post a screenshot of the columns you're talking about, and also maybe manually show what you want your output to be

[AiolosZhao]

Hi @jmcp ,

is this what you want?

Aiolos Zhao

[jmcp]

Hi

Thanks for replying!   i think so ,   but im still not quite sure which option works.

So i have one column containing thousands of Direct and Non-Direct text in the 'cells'  - i can't quite see the option which splits the Non-Direct cells into their own column.

Direct

Non-Direct

[mwimberger]

@jmcp 

I think I get your question now - the two values are direct and indirect are below each other in the same cell. Something like this :

In this case you can still use Power Query and here is how to split by carriage returns:

Hope I understood your question correctly

Cheers

Manfred

[jmcp]

Hi Manfred

Thanks for responding  -  the values are in separate cells but below each other. 

Sorry - i'm having trouble pasting a snip to the page here.

[mwimberger · Accepted solution]

Hi @jmcp 

I think you are after a pivot column function in power query - here are the steps.

Hope this helps. ( and yes, snipping and pasting doesn't work, you need to save an image to upload it)