Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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 1Data 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. 

 

PBI Datatable Example 2.png

 

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

 

PBI Datatable Example 3.png

 

Many thanks for your help.

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @Anonymous 

When adding an index column, i can reprocude your problem.

Capture13.JPG

Using method below, i can get the final result as below:

Capture15.JPG

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.

View solution in original post

12 REPLIES 12
vtad
Regular Visitor

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
Community Support
Community Support

Hi @Anonymous 

When adding an index column, i can reprocude your problem.

Capture13.JPG

Using method below, i can get the final result as below:

Capture15.JPG

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
Community Support
Community Support

Hi @Anonymous 

As tested with your example data, when pivoting columns, it would turn out your expected result directly.

Capture4.JPG

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
Not applicable

@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?

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
Not applicable

@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?

 

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
Not applicable

@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
Community Champion
Community Champion

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

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

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
Not applicable

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))

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.