Forum Discussion

Anonymous's avatar
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 datase...
Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    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.

Mariusz's avatar
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's avatar
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's avatar
    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