Forum Discussion
Error pivoting column.
Q. And was it important to change the type to number for the value column? I wont be able to do that with my data set - Not needed.
When you are pivoting a table, you need to provide a column on the basis of which it can evenly distribute columns (most of time, you will have this kind of column in your data. But you don't have one such column in your data). Since you wanted to have two columns in pivot, hence I had to provide an identifier which provides a sequence 1,1,2,2,3,3,4,4,5,5 and so on.
So first row contains two columns pertaining to 1,1
Second row for 2,2 and so on..
When you do this then you won't encounter the issue of enough elements in the enumeration.
Purpose of #"Added Index" and #"Divided Column" together is to provide this series.
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3tNQ3MjAyUtJRMjI1MQNSTkBsbmgIEvFLzE1VitXBqczSxNIQpKw0Nym1CJ9CMyNzQyLMMzQysiBonhuIaQlm4jMPpMzC1ICw+0AKjYkxzdDQwBSvaUaw0DMxNsBjHlyZmZEJknmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [cs242_dailylogdate = _t, cs242_dailylognumber = _t, WatchType = _t, Value = _t, PivotType = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"cs242_dailylogdate", type date}, {"cs242_dailylognumber", Int64.Type}, {"WatchType", type text}, {"Value", Int64.Type}, {"PivotType", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}),
#"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[PivotType]), "PivotType", "Value"),
#"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
#"Removed Columns"👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
Did as you asked, this is the result =
Can you explain what you have done here and why it worked? I see you added an index column, but it isnt refrenced in the pivot step, so I am not sure what its doing for us.
And was it important to change the type to number for the value column? I wont be able to do that with my data set.
Q. And was it important to change the type to number for the value column? I wont be able to do that with my data set - Not needed.
When you are pivoting a table, you need to provide a column on the basis of which it can evenly distribute columns (most of time, you will have this kind of column in your data. But you don't have one such column in your data). Since you wanted to have two columns in pivot, hence I had to provide an identifier which provides a sequence 1,1,2,2,3,3,4,4,5,5 and so on.
So first row contains two columns pertaining to 1,1
Second row for 2,2 and so on..
When you do this then you won't encounter the issue of enough elements in the enumeration.
Purpose of #"Added Index" and #"Divided Column" together is to provide this series.
👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)
How to get your questions answered quickly -- How to provide sample data
Got this implemented in my report and its working great now, thank you!