Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Transpose date from raw to column

Hi everyone,

 

I have the following issue:

 

Example.JPG

 

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!

2 ACCEPTED SOLUTIONS
Watsky
Solution Sage
Solution Sage

@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.


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.

Proud to be a Super User!

View solution in original post

@Anonymous 

I was able to get to this result :date1.png

using this as my dataset:

date.png

 

by doing the following:

1.) Transposing the table.

trans.png

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

filled.png

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

unpiv.png

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

pivy.pngvalue.png

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:

 

Capture.JPG

 

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

 

Thanks in advance for the support




 


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.

Proud to be a Super User!

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

Thanks Watsky for the solution!

Watsky
Solution Sage
Solution Sage

@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.


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.

Proud to be a Super User!

Anonymous
Not applicable

Hi Watsky, 

 

it should be like this:

 

Capture.JPG

 

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

 

Thanks in advance for the support

@Anonymous 

I was able to get to this result :date1.png

using this as my dataset:

date.png

 

by doing the following:

1.) Transposing the table.

trans.png

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

filled.png

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

unpiv.png

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

pivy.pngvalue.png

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:

 

Capture.JPG

 

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

 

Thanks in advance for the support




 


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.

Proud to be a Super User!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.