Transform data in Power Query

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, 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 

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

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