Unpivoting 3 sets of columns without duplicated data

[emaeorg]

Hi! 

Looking for some advice regarding unpivoting a table with 3 sets of data. I haven't gotten over an issue, where data gets duplicated once you unpivot 2nd and 3rd set. 

Example:

ID	Category 1	Category 2	Category 3	Product 1	Product 2	Product 3	Adjective 1	Adjective 2	Adjective 3
1	Red	Blue	Green	Apple	Peach		Big	Tasty
2	Blue			Banana	Pear	Orange	Small
3	Green	Red		Pear			Round

What I want to do is to unpivot these columns in order to count the categories, product and adjectives as well as use them in slicers. 

From the example table, the preferred result should be:

ID	Category	Product	Adjective
1	Red	Apple	Big
1	Blue	Peach	Tasty
1	Green	null	null
2	Blue	Banana	Small
2	null	Pear	null
2	null	Orange	null
3	Green	Pear	Round
3	Red	null	null

Original source in Sharepoint Excel workbook is updated regularly with new rows. There are some other columns like date, relevant personel and some other categories, where unpivoting is not needed. ID is the only unique value. I can not alter the source. 

Any help is greatly appreciated.

[ThxAlot]

Easy enough,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpKTQGSTjmlqUDKvSg1NQ9IOxYU5ID4AamJyRlAGqQiMx1IhiQWl1SCBGJ1opWMEBqhahLzgBCirwhI+Rcl5qWDpINzE3NyoMpAOo2R7II4AKEJgoLyS/NSYDpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Category 1" = _t, #"Category 2" = _t, #"Category 3" = _t, #"Product 1" = _t, #"Product 2" = _t, #"Product 3" = _t, #"Adjective 1" = _t, #"Adjective 2" = _t, #"Adjective 3" = _t]),
    #"Added Column" = Table.AddColumn(Source, "tb", each let row=Record.ToList(_) in Table.FromColumns(List.Split(List.Skip(row),3))),
    #"Expanded tb" = Table.ExpandTableColumn(Table.SelectColumns(#"Added Column",{"ID", "tb"}), "tb", {"Column1", "Column2", "Column3"}, {"Category", "Product", "Adjective"})
in
    #"Expanded tb"

[Ahmedx · Accepted solution]

pls try this

[emaeorg]

Thanks, this works great!