Forum Discussion

pedroccamaraDBI's avatar
pedroccamaraDBI
Icon for Post Partisan rankPost Partisan
4 years ago
Solved

Pivot or unpivot or transpose

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

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

4 Replies

  • jennratten's avatar
    jennratten
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
    BA_Pete
    Icon for Super User rankSuper User
    4 years ago

    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