cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmcp Frequent Visitor
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mwimberger Member
Member

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

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) 


2019-07-22_19-21-22.jpg

View solution in original post

7 REPLIES 7
Super User I
Super User I

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

Hi @jmcp ,

 

is this what you want?

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

 

Aiolos Zhao

Highlighted
silver3895 Regular Visitor
Regular Visitor

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

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

jmcp Frequent Visitor
Frequent Visitor

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

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 Member
Member

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

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

 

mwimberger Member
Member

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

@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 :

2019-07-22_17-23-14.jpg

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

2019-07-22_17-26-26.jpg

Hope I understood your question correctly

 

Cheers

 

Manfred

jmcp Frequent Visitor
Frequent Visitor

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

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 Member
Member

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

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) 


2019-07-22_19-21-22.jpg

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors