Power query transformation

Power Query
1

Hi, I am trying to separate out the attributes of a part from the initial format ( Green Border Table ) in screenshot below , to the purple table format , provided an excel file of demo data , please let me know if some content is already available on the same to convert it to the required format .

image
image798×340 28.9 KB

Food Attributes.xlsx (11.1 KB)

2

Hi @Unni,

The difficult part of this transformation seems to be the grouping, I created two ways to group these items and you’ll have to determine what makes the most sense… the end result is the same

image
image839×155 5.49 KB

.
With a key you can group items, used that to create a record with the values of intrest.

image
image1268×201 8.8 KB

And expanded the values…

.
Here’s your sample file.
eDNA - Menu Attributes.pbix (12.5 KB)

I hope this is helpful

1 Like
4

Hi @Melissa Once again for the super quick response thanks a lot… !! Ofcourse it is a great help :slight_smile:
Also i would like to learn more on how to write such M scripts , can you suggest some resource.

5

@Unni ,

Have you taken @Melissa 's Power Query/M course on the portal yet? That’s an awesome place to start, because it gives you a great foundation on tables, lists, functions, etc. - all the key building blocks of M code.

There’s also a Power Query Series on the portal, which is basically just an organized collection of the Expert videos (mainly Melissa) on PQ/M.

  • Brian
1 Like
6

Hi @Melissa , Try to implement it then noticed it is not complete will need one more step as shown below to get to the results i expected, tried but unable to figure out , please let me know if it can done , attached the pbix
Kettle Attributes.xlsx (12.7 KB)
Kettle.pbix (25.6 KB)

image
image1331×248 6.5 KB

7

Hi @BrianJ, Looked into it when it was released initially, looking forward to complete both of these asap & do some cool transformations , thanks for your guidance.

1 Like
8

Hi @Unni,

Give this a go. Paste the full code below into a new blank query.

let
    AttColNames = List.Select( Table.ColumnNames( Source ), each Text.StartsWith( _, "Attribute")),
    NewColNames = List.Distinct( List.RemoveMatchingItems( List.Combine( List.Transform( NewRecord[Custom], Record.FieldNames )), Table.ColumnNames(Source) )),
    Source = Table.TransformColumnTypes( Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUfJOLSnJSQUynBMLdBUMfYCsgPzy1CJdQ4NwkGh+jm5wZk5ZapFSrA5YE1DQKT+lEkj5JpboBgejKtJRAqszgpidl58ElQ7PyCxJheoJyEksLslMVoApNgYrDs8sAilwL01MTwVaDlXrXApk5KTmpWeAxGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part Number " = _t, #"Long Description" = _t, #"Attribute1 " = _t, Attribute2 = _t, Attribute3 = _t]), {{"Part Number ", Int64.Type}}),
    NewRecord = Table.RemoveColumns( Table.AddColumn( Source, "Custom", each 
        Record.RemoveFields(
            Record.RenameFields( _, 
                List.Zip(
                    {
                        Table.SelectRows( Table.UnpivotOtherColumns( Table.FromRecords( { Record.SelectFields( _, AttColNames ) } ), {}, "Attribute", "Value"), each ([Value] <> ""))[Attribute],
                        List.Transform( List.Select( Record.ToList( Record.SelectFields( _, AttColNames )), each _ <> "" ), each Text.Proper( Text.BeforeDelimiter( _, "-")))
                    }
                )
            ), Table.SelectRows( Table.UnpivotOtherColumns( Table.FromRecords( { Record.SelectFields( _, AttColNames ) } ), {}, "Attribute", "Value"), each ([Value] = ""))[Attribute]? )
        ), AttColNames ),
    UpdateTable = Table.ExpandRecordColumn( NewRecord, "Custom", NewColNames, NewColNames)
in
    UpdateTable

.
With this result.

image
image1314×114 6.03 KB

I hope this is helpful

1 Like
9

Thanks a lot @Melissa it is very helpful… :smiley: