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

Create multiple colums from text values in one colum

Hello,

 

I have a table with articles, the problem is that i have one colum(kenmerk2)  filled with header names, this headers should be seperate coulms.

 

ArticlevalueHeadername
123456125Sales Price
123456100Buying price
123456250Buying quantity

 

Is should be something like this, then i can use the colums in my dashboard for the sum of Sales Price or Quantity.

 

ArticleSales PriceBuying priceBuying quantity
123456125100250

 

BI screenshot.JPG

 

Does anyone have a good idea for this?

 

Regards Björn

1 ACCEPTED SOLUTION

Accepted Solutions
bkoenen Frequent Visitor
Frequent Visitor

Re: Create multiple colums from text values in one colum

Hello Pattem,

 

This works for 90%, now the grouping increase my dates till only one date, but I want to keep the dates so that I can see my what happens monthly. So that the values per month are in one row, like the table below.

 

ArticleSales PriceBuying priceBuying quantityDate
1234561251002501-1-2018
1234565025102-1-2018
123456200100253-1-2018
1234561251002504-1-2018
1234561251002505-1-2018
1234561251002506-1-2018

 

Regards Björn

7 REPLIES 7
Super User
Super User

Re: Create multiple colums from text values in one colum

@bkoenen

 

Use the Pivot feature within Power Query

 

 

Capture.PNG

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Highlighted
bkoenen Frequent Visitor
Frequent Visitor

Re: Create multiple colums from text values in one colum

Hi Livio,

 

That works fine, thank you.

 

Now the next question is, can I put the rows together with the same article number and same date, then I have one row with the colums filed with the values.

 

Now I have this

ArticleSales PriceBuying priceBuying quantityDate
123456125nullnull1-1-2018
123456null100null1-1-2018
123456nullnull2501-1-2018

 

and I would like to have this,

 

ArticleSales PriceBuying priceBuying quantityDate
1234561251002501-1-2018

 

regards Björn

Super User
Super User

Re: Create multiple colums from text values in one colum

@bkoenen Please use "Group By" option in "Power Query Editor, with MAX on each field

 

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Re: Create multiple colums from text values in one colum

Strange, using the sample data, I only get one row.

 

My formula bar for the pivoting step looks like this:

 

= Table.Pivot( PreviousStep , List.Distinct(PreviousStep[Headername]), "Headername", "value")

 

In case you have issues with duplicate values, you can click on Advanced Options and pick another aggregation function for the pivot operation (SUM, MIN, ...)

bkoenen Frequent Visitor
Frequent Visitor

Re: Create multiple colums from text values in one colum

Hello Pattem,

 

This works for 90%, now the grouping increase my dates till only one date, but I want to keep the dates so that I can see my what happens monthly. So that the values per month are in one row, like the table below.

 

ArticleSales PriceBuying priceBuying quantityDate
1234561251002501-1-2018
1234565025102-1-2018
123456200100253-1-2018
1234561251002504-1-2018
1234561251002505-1-2018
1234561251002506-1-2018

 

Regards Björn

Re: Create multiple colums from text values in one colum

Hi Björn,

 

the pivoting step should be enough to get the desired result.

 

An additional grouping operation is not required.

 

Can you please provide a link ot a sample file, so we can look into it?

Regards

bkoenen Frequent Visitor
Frequent Visitor

Re: Create multiple colums from text values in one colum

Hi Laurent,

 

The solutions from Pattern work for me, thank you for your response.

 

regards Björn