Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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.
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!
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?
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
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.
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"
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
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))