Why my data is totally wrong after the unpivoted the selected columns in Power Query

[Aina-]

Dear all, 

I have the trade data from January until December in which the months (Jan-Dec) are the header. How do I make the header as a month's slicer ? Is it using the pivot and unpivot ? , but I have 9,910 rows of each columns. I cant provide the PBIX file since it is confidential. 

I tried to unpivoted the selected column (all months), it works but the total(sum) of import and export changed after I unpivoted the selected column and the data become totally wrong. The import data should be 17bil, but it become 2 tril after unpivoted the selected columns

 

[v-stephen-msft · Accepted solution]

Hi @Aina- ,

You need to use pivot first and then unpivot.

1.Select the two columns and then click "Unpivot other columns".

2.Split the Attribute column with " ".

3.Select the Attribute.1 column and click Pivot Column.

Now you have a column with month name, a column with QTY and a column with VAL.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Aina-]

@JihwanKim @FreemanZ