Pivot or unpivot or transpose

[pedroccamaraDBI]

Hi guys,
I hope everyone's ok
I got this need to transform these 4 columns on the right into 2 columns only according to this pic beside.

Is it possible? Could you let me know how could i do it? I've try transpose, pivot and unpivot.....
Thanks in advance
Best regards

[mahoneypat]

One good way to do this would be to create a simple custom list of records as shown in this video.

Faster Data Transformations with List/Record M Functions - YouTube

Pat

[jennratten]

This is how you can accomplish the task.  Instead of pivot/unpivot you can simply stack the tables.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSpKTQGSIJahvqG+kYGREZBpDOYaQbixOtFKIMGknNJUIGWEqtQEXSlIb3pRamoeEcaCNOcXJealE2OwKVCgoLSoICeVkMmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Color = _t, Quantity1 = _t, Date1 = _t, Quantity2 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", Int64.Type}, {"Color", type text}, {"Quantity1", Int64.Type}, {"Date1", type date}, {"Quantity2", Int64.Type}, {"Date2", type date}}),
    Table1 = Table.RenameColumns ( Table.SelectColumns(#"Changed Type",{"Id", "Color", "Quantity1", "Date1"}), {{"Quantity1", "Quantity"}, {"Date1", "Date"}}),
    Table2 = Table.RenameColumns ( Table.SelectColumns(#"Changed Type" ,{"Id", "Color", "Quantity2", "Date2"}), {{"Quantity2", "Quantity"}, {"Date2", "Date"}}),
    CombineTables = Table.Combine ( { Table1, Table2 } )
in
    CombineTables

Before:

AFTER:

[BA_Pete · Accepted solution]

Hi @pedroccamaraDBI ,

I would create two separate queries then append them together.

1) Duplicate your original query

2) In your original query, remove columns 2 and 3

3) In your duplicate query, remove columns 5 and 6

4) In both queries, change column names so that they match EXACTLY (spelling, case etc.) to one another e.g. [SKU], [Type], [Value], [Week & Year]

5) Select your original query and go to Home tab > Append Queries, and choose whether you want to append the second table onto the bottom of the first, or create a brand new query that is the two source queries combined.

Pete

[pedroccamaraDBI]

Hello @BA_Pete 
Thank you for your answer. I too prefer a simple solution,
Stay safe

Best regards