Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bkoenen
Helper I
Helper I

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

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

View solution in original post

7 REPLIES 7
LivioLanzo
Solution Sage
Solution Sage

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

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

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 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 PBI Community Champion




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

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

Hi Laurent,

 

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

 

regards Björn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.