Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

Pivoting and removing null values from multiple columns

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.

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.

2Pivoting and removing null.pbix96 KB

12 Replies

v-juanli-msft

Community Support

6 years ago

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.

2Pivoting and removing null.pbix96 KB

Anonymous
Not applicable
6 years ago
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))
Mariusz
Community Champion
6 years ago
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
  Not applicable
  6 years ago
  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
    Community Champion
    6 years ago
    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
v-juanli-msft
Community Support
6 years ago
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.
Pivoting and removing null values from multiple columns.pbix68 KB
- Anonymous
  Not applicable
  6 years ago
  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
    Community Champion
    6 years ago
    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
vtad
Regular Visitor
4 years ago
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!

Forum Discussion

Pivoting and removing null values from multiple columns

12 Replies

Related content

How to pivot Multiple Columns

Pivot multiple columns

Pivot issue

Pivot / Unpivot?

HELP WITH PIVOTING MULTIPLE COLUMNS