Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My spreadsheet contains a lot of dates as colums and I want to 'unpivot' these so they collapse into one column. When I select the columns that I want to be affected, the whole spreadhseet ends up changing, even the columns I did not select.
I can't figure out what is causing the change? I worked on an older version of this spreadsheet and never had this problem.
I have attached two images showing a before and after.
The dates to the right are what I want to condense into one column. the information to the left I can organise properly in the visualisation.
I am selecting the columns to the left as there is only about 7/8 and then doing Transform>Unpivot Other Columns.
But this changes the whole spreadsheet as you can see below.
Any tips would be much appreciated!
Cheers
Solved! Go to Solution.
Hi @Anonymous
Please note that definition of unpivot column:
If you'd like to merge several columns into one column,I'd like to suggest below M code: (Select Date1&2&3, then unpivot)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDQ0t9Q30jIBOEYnWilZJAwhA+VNYYJpUMZBhDlcJlTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Date1 = _t, Date2 = _t, Date3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1", "Column2"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] <> null and [Value] <> "") in #"Filtered Rows"
Hi @Anonymous
Please note that definition of unpivot column:
If you'd like to merge several columns into one column,I'd like to suggest below M code: (Select Date1&2&3, then unpivot)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYiMDQ0t9Q30jIBOEYnWilZJAwhA+VNYYJpUMZBhDlcJlTZRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Date1 = _t, Date2 = _t, Date3 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1", "Column2"}, "Attribute", "Value"), #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each [Value] <> null and [Value] <> "") in #"Filtered Rows"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |