Power query transformation

[Unni]

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 .

Food Attributes.xlsx (11.1 KB)

[Melissa]

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

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

And expanded the values…

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

I hope this is helpful

[Unni]

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

[BrianJ]

@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.

Power Query Series

• Brian

[Unni]

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)

[Unni]

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.

[Melissa]

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.

I hope this is helpful

[Unni]

Thanks a lot @Melissa it is very helpful…