Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
I want to convert column "Value" to 3 column "Product" "Owner" and "Size"
Solved! Go to Solution.
@Anonymous
I have attached the file with the transformation.
You can download the file: HERE
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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.
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
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.
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.
Hi @ziying35 ,
Thanks for your advice. However, I am new in Power BI so it quite complicated for me to understand. 😞
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
Hi @ziying35,
I'd like to add the sample data as below. Please advise the solution.
Thanks for your support!
Original data:
Month | Week | Value |
Jan | W1 | Table |
Jan | W1 | Jack |
Jan | W1 | Small |
Jan | W1 | Chair |
Jan | W1 | Joe |
Jan | W1 | Large |
Jan | W2 | Chair |
Jan | W2 | Carol |
Jan | W2 | Small |
Feb | W5 | Table |
Feb | W5 | Jessica |
Feb | W5 | Large |
Mar | W9 | Table |
Mar | W9 | Marry |
Mar | W9 | Small |
Mar | W10 | Chair |
Mar | W10 | Jack |
Mar | W10 | Large |
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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!
Product | Size |
Table | Small |
Chair | Large |
Original data:
Month | Week | Value |
Jan | W1 | Table |
Jan | W1 | Jack |
Jan | W1 | Small |
Jan | W1 | Chair |
Jan | W1 | Joe |
Jan | W1 | Large |
Jan | W2 | Chair |
Jan | W2 | Carol |
Jan | W2 | Small |
Feb | W5 | Table |
Feb | W5 | Jessica |
Feb | W5 | Large |
Mar | W9 | Table |
Mar | W9 | Marry |
Mar | W9 | Small |
Mar | W10 | Chair |
Mar | W10 | Jack |
Mar | W10 | Large |
@Anonymous
I have attached the file with the transformation.
You can download the file: HERE
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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!
@Anonymous
You find this command under TRANSFORM tab as "Pivot Column
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group