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