Forum Discussion

Funk-E-Guy's avatar
Funk-E-Guy
Icon for Helper II rankHelper II
1 year ago
Solved

Power Query - Unpivot multiple value groups into multiple value columns?

I have a department that really like to forecast in a tabular format, but they split monthly quantities into two groups of columns:       Organic Promotion Country Item Jan Feb Mar Ap...
Need Help
  • Funk-E-Guy's avatar
    Funk-E-Guy
    1 year ago

    Thank you both, Ashish_Mathur and lbendlin. For confidentiality I kept my data vague, but your comments pointed me in the right direction.

     

    To help others, here's the generalized explanation of what I did:

     

    1. Instead of having "Super Headers" for "Organic" and "Promo", I changed the table so that the monthly headers said "Jan Organic", ... , "Dec Promo"
    2. I unpivoted all 24 columns together. This gave me 24 rows for each entry (it'll be 12 by the end).
    3. A new "Attribute" column appears that contains "Jan Organic", ... , "Dec Promo"
    4. I add a new column "Month" that pulls text before delimiter " " (a single space). This isolates "Jan" thru "Dec".
    5. I add a new column "Pivot Headers" that pulls text after delimiter " " (a single space). This isolates "Organic" & "Promo"
    6. I delete the "Attribute" column
    7. I select "Pivot Headers" and pivot, choosing my value column as... the value column.

     

    In general, you're wanting to unpivot and then re-pivot. The key is to get your desired resulting pivoted headers into a single "Pivot Headers" column. So if you want to end up with 4 groups of values, the "Pivot Headers" needs to have the 4 distinct values.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago

In your curent form your data is not usable as it would result in duplicate column names which Power Query then butchers.

Can you please check and post the actual raw data format? Maybe more like this?

 

// Table (2)
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlDSAWP/ovTEvMxkIIsgCijKz80vyczPI6w4VidayTm/NK+kqBLI9SxJzQVSXokgnW6pSUDSN7EISDoWFIHZIEVepXlgMgckXpoOJINTC0AOTC4Bkn75ZUDSJTWZauaAnBgaDOSEuAaHeIa4+gKZhgYGpJGmINIIzDY2QLCRsSkyxxBDGqYhNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t, Column22 = _t, Column23 = _t, Column24 = _t, Column25 = _t, Column26 = _t])
in
    Source