Transpose date from raw to column

[Anonymous]

Hi everyone,

I have the following issue:

On the first raw there is the year and on the second the month for the column TotalSales and TotalPrice (the first two columns are referred to TotalSales and TotalPrice for June 2018, the third and fourth for July 2018 etc..). Is there any way to transpose the date on the format "mm-yyyy" next to the single columns TotalSales and TotalPrice?

Thank  you in advance!

[Watsky · Accepted solution]

@Anonymous what should the end result look like?

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]

Thanks Watsky for the solution!

[Watsky · Accepted solution]

@Anonymous what should the end result look like?

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]

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

[Watsky · Accepted solution]

@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

---