Forum Discussion
Anonymous
6 years agoNot 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 ...
ziying35
Impactful Individual
6 years agoHi, 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.
- Anonymous6 years agoNot applicable
Hi ziying35 ,
Thanks for your advice. However, I am new in Power BI so it quite complicated for me to understand. 😞