Forum Discussion
Pivoting and removing null values from multiple columns
Hi Anonymous
When adding an index column, i can reprocude your problem.
Using method below, i can get the final result as below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZGxCsIwFEX/JXMlaWqFji2IbhUdHEqHaF+lGBKIQfTvzdSkJU3sdt9wOLx7mwaVJUoQJWmBM0y3Ti5MPjLR8UE8TDxJqVCbRID6DYpxbtIZ2AsE3DjMqB2muZNnmoOUXRy4aAC1CKQEZ2Q8UpxPFfvPHTgI/QfleFZQtoQJVFUrmw4CVnJVg4ZN3fczKNybx+IBrKV+sm98f0fgAYKCccjg20uLBL/wjt/+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date1 = _t, Date2 = _t, Area = _t, Rating = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date1", type date}, {"Date2", type date}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Area]), "Area", "Rating"), #"Grouped Rows" = Table.Group(#"Pivoted Column", {"ID", "Date1", "Date2"}, {{"Handing2", each _, type table [ID=text, Date1=date, Date2=date, Index=number, Handling=text, Overall=text, Steering=text]}, {"Overall2", each _, type table [ID=text, Date1=date, Date2=date, Index=number, Handling=text, Overall=text, Steering=text]}, {"Steering2", each _, type table [ID=text, Date1=date, Date2=date, Index=number, Handling=text, Overall=text, Steering=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Handing", each Table.SelectRows([Handing2],each [Handling] <> null)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Overall", each Table.SelectRows([Overall2],each [Overall] <> null)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Steering", each Table.SelectRows([Steering2],each [Steering]<>null)), #"Expanded Handing" = Table.ExpandTableColumn(#"Added Custom2", "Handing", {"Handling"}, {"Handing.Handling"}), #"Expanded Overall" = Table.ExpandTableColumn(#"Expanded Handing", "Overall", {"Overall"}, {"Overall.Overall"}), #"Expanded Steering" = Table.ExpandTableColumn(#"Expanded Overall", "Steering", {"Steering"}, {"Steering.Steering"}), #"Removed Columns" = Table.RemoveColumns(#"Expanded Steering",{"Handing2", "Overall2", "Steering2"}) in #"Removed Columns"Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous
As tested with your example data, when pivoting columns, it would turn out your expected result directly.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZGxCsIwFEX/JXMlaWqFji2IbhUdHEqHaF+lGBKIQfTvzdSkJU3sdt9wOLx7mwaVJUoQJWmBM0y3Ti5MPjLR8UE8TDxJqVCbRID6DYpxbtIZ2AsE3DjMqB2muZNnmoOUXRy4aAC1CKQEZ2Q8UpxPFfvPHTgI/QfleFZQtoQJVFUrmw4CVnJVg4ZN3fczKNybx+IBrKV+sm98f0fgAYKCccjg20uLBL/wjt/+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date1 = _t, Date2 = _t, Area = _t, Rating = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Area]), "Area", "Rating")
in
#"Pivoted Column"Could you tell me how you pivot the model?
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
For some reason or other, I had an index column in my original dataset that was messing up the merging of the columns during pivoting. It's all working now except for one error:
I'm getting a few errored cells across my dataset. Any idea of why that could be?
- Hello
These errors normally are coming from a Table.TranformColumsTypes as this function converts the data type and if it's not possible to do so, you get an error. Please check this and give feedback
JimmyHi Jimmy,
I've checked a few of the errors and the message is the same:
"Expression.Error: There were too many elements in the enumeration to complete the operation. Details: List."
I've checked the data to see if there were any inconsistent values, but there isn't any. The range I've specified is the only list of options, so I can't imagine one Rating value would be, for example, factorial and nominal.
I've found that some of the IDs have duplicate values. Now I've identified the issue, how could I remove them? Should I do that prior to or after pivoting?
Hello Anonymous
the problem is that I have no clue of your data source and the steps applied. So I can't give any answers on that.
If you could post here a data source example (hiding sensitive data) and the code you are applying we can see where these errors are coming from.
Jimmy
