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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.