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
Anonymous
Not applicable

Transform data in Power Query

Dear all,

I would like to ask your help on my issue.

I have a data as attached with column "Value" contains 3 groups of data "Product", "Owner", "Size". Please your help to advise if it's possible to split data in column "Value" to three column "Product", Owner" and "Size" in Power Query.

Thanks for your support!

 

Original data: 

Original dataOriginal data

I want to convert column "Value" to 3 column "Product" "Owner" and "Size"

 

image.png

1 ACCEPTED SOLUTION

@Anonymous 

I have attached the file with the transformation.

You can download the file: HERE



Fowmy_1-1597258635489.png

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

12 REPLIES 12
orlandom
Advocate I
Advocate I

Hi @Anonymous ,

If you're new to Power BI there's an alternative you could try in the August 2020 version of Power BI Desktop. The feature is called Text from Examples.

The steps are as follows:

Enable the Text from Examples feature in Power BI
1) Download/Update Power BI Desktop to the August 2020 version
2) Open Power BI Desktop -> Go to File -> Options & Settings -> Options -> Preview Features -> Mark on Import Text Using Examples -> Click OK
3) Close and open Power BI Desktop

Import the data

1) Save the data as a text or csv.
2) Go to Home -> Get Data -> Text/CSV and browse for the file
3) On the bottom-left corner -> click on Extract Table Using Examples

orlandom_0-1597323109442.png

4) Then type the expected column names and enter a few examples until Power BI gets the full pattern. Notice that depending on the complexity of the data you'll need to enter more examples. In the picture below, the values in black were entered by me and the ones in gray were extracted by Power BI.

orlandom_1-1597323327886.png

 

Hopefully your dataset is bigger than the example provided so you'll take advantage of the automation.

Hope this helps!
Orlando Mezquita
www.MasterDataAnalysis.com

Anonymous
Not applicable

Hi @orlandom ,

 

Thanks for your advice.

Actually, my data is complicated than example so I may not apply it in this case, but it's great techniques.

 

ziying35
Impactful Individual
Impactful Individual

Hi, @Anonymous

 

 

// output
let
    Source = Table.FromRecords(Json.Document(Binary.Decompress(Binary.FromText("i65W8s3PK8lQslLySsxT0lEKT03NBnLCDYHssMSc0lQgJyQxKSdVqVaHGLVeicnZRCoNzk3MySFSrXNGYmYRsU7IJ9axPolF6fjUGpHgAhS1iUX5+HxmhDcU3FKTkNSa4o0I3Gq9UouLM5MTiVSNGRK+iUVIai3xugK3WqBMUSWRajFDAlWtoQHe6MCjGCNR4lELDYlYAA==",BinaryEncoding.Base64),Compression.Deflate))),
    fx = (tbl)=> Table.ToRows(tbl[[Month],[Week]]){0}&tbl[Value],
    result = Table.FromRows(List.Transform(Table.Split(Source, 3), fx), List.Skip(Table.ColumnNames(Source))&{"Product", "Owner", "Size"})
in
    result

 

My solution is to split the table every three rows, and then transform each table element in the list.

"Table.ToRows(tbl[[Month],[Week]]){0}&tbl[Value]" This expression converts the first row of the Month and Week columns of each split table into a list, and then merges it with the Value list of the split table.

 

 

Anonymous
Not applicable

Hi @ziying35 ,

 

Thanks for your advice. However, I am new in Power BI so it quite complicated for me to understand. 😞

 

ziying35
Impactful Individual
Impactful Individual

Hi, @Anonymous 

Can you post sample data? Don't post pictures, it's too tiring to hit the keyboard in light of them, this case is easy to solve

Anonymous
Not applicable

Hi @ziying35,

 

I'd like to add the sample data as below. Please advise the solution.

 

Thanks for your support!

 

Original data:

MonthWeekValue
JanW1Table
JanW1Jack
JanW1Small
JanW1Chair
JanW1Joe
JanW1Large
JanW2Chair
JanW2Carol
JanW2Small
FebW5Table
FebW5Jessica
FebW5Large
MarW9Table
MarW9Marry
MarW9Small
MarW10Chair
MarW10Jack
MarW10Large
Fowmy
Super User
Super User

@Anonymous 

Please share a table that specifies the items that come under Products, Owners and Size?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Dear @Fowmy ,

I'd like to attached original data as below. The items under "Value" can be specified in 3 groups "Products", "Owners" and "Size". However, I can only define items under "Products" and "Size", the rest is belong to "Owners".

Kindly advise.

Thank you!

 

ProductSize
TableSmall
ChairLarge

 

Original data:

MonthWeekValue
JanW1Table
JanW1Jack
JanW1Small
JanW1Chair
JanW1Joe
JanW1Large
JanW2Chair
JanW2Carol
JanW2Small
FebW5Table
FebW5Jessica
FebW5Large
MarW9Table
MarW9Marry
MarW9Small
MarW10Chair
MarW10Jack
MarW10Large

 

@Anonymous 

I have attached the file with the transformation.

You can download the file: HERE



Fowmy_1-1597258635489.png

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 



Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Hi @Fowmy 

 

Thank you very much for your support!.

I also have another question related to Power Query, please help to advise.

https://community.powerbi.com/t5/Power-Query/Transform-data-in-matrix/m-p/1294132

 

Anonymous
Not applicable

Dear @Fowmy ,

Thank you very much for your advice.

However, I am very new with Power BI, could you please help to clarify about this step? It is set by M language or select which tab in toolbars. And the meaning of this step.

Thanks a lot!

 

Nguyen_0-1597321008626.png

 

 

@Anonymous 

You find this command under TRANSFORM tab as "Pivot Column

Fowmy_0-1597324017961.png

 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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.

Top Solution Authors
Top Kudoed Authors