Pivoting and removing null values from multiple columns

[Anonymous]

Hello,

I'm new to PowerBI and I've been tasked with performing (what currently seems like) some rather complex data-engineering. So I hope somebody could help a newbie.

I've got a huge dataset with a structure similar to the one in the image (see below). Due to sensitivity of the original data, I can only post a mock example.Data table example 1

My objective was to pivot the Area column with the Area names as the column headers and the ratings, in the Rating column, as the value. This was the simple part, but then I realised I needed a way to remove Null fields from multiple columns simultaneously (see example-snippet in the image below). I realise the Audit and Published Date columns are part of the reason for the confusion, but I need to retain these for analysis. 

So this is where I need help:

a). How can I combine the values spread across multiple rows into one row? So that my table would look like the image below, i.e. all the values for a given ID sit within one row, without nulls inbetween.

  - Could I ask for a generic answer so I could apply to my original dataset

Many thanks for your help.

[vtad]

Hello!

I am having the same issue as Original Poster. However, when I pivot there are about 140 columns and for hence there are several rows with null values.

How can I combine the values spread across multiple rows into one row?

If someone please explain the method instead of directly posting the solution, it would be really helpful as I am trying to understand how to solved for this issue.

Thank you!

[v-juanli-msft · Accepted solution]

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.

[v-juanli-msft]

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.

[Anonymous]

@v-juanli-msft @Jimmy801

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?

[Jimmy801]

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
Jimmy

[Anonymous]

@Jimmy801 

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?

[Jimmy801]

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

[Anonymous]

@Jimmy801 

Hi 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.

[Mariusz]

Hi @Anonymous 

You can try something like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyMtE3MNY3MjC0BHI8EvNScjLz0oHMgPz8IqVYHWyq/MtSixJzcoCsoNTE4tS81KScVCSlpvoGZlgMdM/PT8GhKrgkNbUImypjA31DAyxmuVYkp+ak5pXgUIlkHgGVCK+gKHRyIiZkMFUhjAsvyixJ1fVPS0NSicPLaOYhq0KY55+dWKkUGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Area = _t, Rating = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date", type date}, {"Area", type text}, {"Rating", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Area]), "Area", "Rating"),
    #"Filter Rows" = Table.SelectRows( #"Pivoted Column", each not List.Contains( { [Handling], [Overall], [Steering] }, null ) )
in
    #"Filter Rows"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Hi @Mariusz ,

Thanks for your advice.

I tried you're code, but I don't think I've properly explained myself in my original post (I've made the edit now). Basically, I find your code removes all rows which have 'Null' in at least one of my pivot columns (Handling, Steering, Overall) and only rows with all three values present remain. 

However, what I wanted to achieve was to combine all the values from the pivot columns in one row. So, if you look at my second screenshot image, you'll see that each row displayed actually has a component of what I need for a full row to be populated. The problem is that the Pivot function has made the values for one ID (e.g. AA) spread across three columns, whereas I want them all in one row. Does that make sense? If not, I'll try to clarify further. Thanks

[Jimmy801]

Hello @Anonymous 

don't get really the point.

If column ID, Audit date and publish date are all exactly the same, there will be only one row. See this example here

let
    Quelle = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lEyMtE1MNY1tAQxLeFMj8S8lJzMvHQgMyA/v0gpVgev8uCS1NQiiHL/7MRKQsr9y1KLEnNygKyg1MTi/LzEpJxUpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, #"Audit date" = _t, #"Published Date" = _t, Area = _t, Rating = _t]),
    Pivot = Table.Pivot(Quelle, List.Distinct(Quelle[Area]), "Area", "Rating")
in
    Pivot

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Anonymous]

In PowerQuery, add a custom column with the function below. You can change the boldface to include or exclude the attributes which you want to test. It can also be generated from a table or a dynamic method if you have some rules.

not List.IsEmpty(List.Select(Record.ToList(
Record.SelectFields(_,{"Handling","Steering","Overall"})), each _= null))