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
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!
Solved! Go to Solution.
@Anonymous what should the end result look like?
Proud to be a Super User!
@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
Proud to be a Super User!
Thanks Watsky for the solution!
@Anonymous what should the end result look like?
Proud to be a Super User!
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
@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
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |