Transpose date from raw to column

Anonymous what should the end result look like?

Anonymous 

I was able to get to this result :

using this as my dataset:

by doing the following:

1.) Transposing the table.

2. Highlighted the date and year columns then doing fill down.

3. Highlighting the three value columns then doing unpivot columns.

4. Highlighting the TotalSales/TotalPrice column then pivoting the column using the new value column.

5. Removed the unnecessary column.

6. Renamed the columns to have Year and Date as Column names.

Here's what the M code looks like:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtFDSUUJBsTrRSmYQtjmEsoAJh+SXJOYEJ+akFgNFwJyAoszkVBiHJBmQeYYGQDFDAxBpbgFhQkSgwiA1RiCOEUTCGOQuUwgbLG5iClVlDJY2hcgYwmR0IOIQVbEA", 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]),
#"Transposed Table" = Table.Transpose(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Transposed Table",{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", Int64.Type}, {"Column6", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Column1", "Column2"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Filled Down", {"Column1", "Column2", "Column3"}, "Attribute", "Value"),
#"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Column3]), "Column3", "Value", List.Sum),
#"Removed Other Columns" = Table.SelectColumns(#"Pivoted Column",{"Column1", "Column2", "TotalSales", "TotalPrice"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Column1", "Year"}, {"Column2", "Month"}})
in
#"Renamed Columns"

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.

---

Anonymous wrote:

Hi Watsky, 

 

it should be like this:

 

 

With the date on two columns (year and month) that are on the two first raws right now. 

 

Thanks in advance for the support

---