Remove null and shift data up

[alexanderc]

Good day.

I am attempting to do more manipulation (automatically) in Power BI rather than having to run a raw data source through an Excel setup to get what I need.

When doing so, I am pulling data that comes into Power BI like this:

What I would like to figure out how to do - and I don't even know the terminology, so I'm sincerely sorry, is to get each of the "null" values to be eliminated, and then shift non-nulls 'up' so that I end up getting the horizontal data sets.

So that I get something like this (this is Excel):

Is there a way to do that? I'm quite new to data manipulation, so I appreciate the help.

Thank you.

[PhilipTreacy · Accepted solution]

Hi @alexanderc 

Download examplePBIX file here

It's always better to supply some sample data rather than screen shots.  Your results don't match the data in the screenshots either but I've done what I can to match them.The process will be similar with the real data.

You can create a Custom Column with a 1 where there's a name in Column2 (from your screenshot).  Then Fill Up all columns, and filter the Index column to only leave 1's.  You'll end up with something like this.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nY/BCsIwDIZfJfTcl5gio+CpCB7GDqENMzgyadc9vxHcwUMEhf+Q5P8T8g2Dc951WBbxcG53rNp+aPTDu1SbDOt1I628kWGqLpRuwolRIBgR1RVXKhCpLq0kqhBEezHCqj5G6AtKhogPztWI7eNuajxTQQ+Hop/YrP9znmRiIYUIXzD15dwUFY688Qz7zi+c4xM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Labour Detail" = _t, Column2 = _t, Column1 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column2", "Column1", "Column3", "Column4", "Column5", "Column6"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Column2] <> null then 1 else 0),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Column2", "Column1", "Column3", "Column4", "Column5", "Column6"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Employee Labour Detail"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Column2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Column2.1", Text.Trim, type text}, {"Column2.2", Text.Trim, type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Trimmed Text",{"Column1", "Column2.2", "Column2.1", "Column3", "Column4", "Column5", "Column6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column2.2", "First"}, {"Column2.1", "Last Name"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "First Name", each [First]),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Column1", "First", "First Name", "Last Name", "Column3", "Column4", "Column5", "Column6"}),
    #"Added Custom2" = Table.AddColumn(#"Reordered Columns1", "Name", each [First Name] & " " & [Last Name]),
    #"Reordered Columns2" = Table.ReorderColumns(#"Added Custom2",{"Column1", "First", "First Name", "Last Name", "Name", "Column3", "Column4", "Column5", "Column6"}),
    #"Added Custom3" = Table.AddColumn(#"Reordered Columns2", "Name (L, F)", each [Last Name] & ", " &[First Name]),
    #"Reordered Columns3" = Table.ReorderColumns(#"Added Custom3",{"Name (L, F)", "Column1", "First", "First Name", "Last Name", "Name", "Column3", "Column4", "Column5", "Column6"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns3",{{"Column1", "Preferred Name"}, {"Column3", "Status"}})
in
    #"Renamed Columns1"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[alexanderc]

Amazing. Thank you so much! The quick directions you gave were great and got me to where you were leading me. Thank you, thank you!

[amitchandak]

@alexanderc , grouping rows can help - https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/