How to remove Null values from a column without affecting the entire row

[Andresgamez28]

Hello,

I have the next table with null values, how can I move the values to only one row without affecting the data?

Table to fix:

What I need to get as a result:

Thank you.

[AlienSx · Accepted solution]

let
    Source = your_table,
    func = (tbl) => 
        [cols = Table.ToColumns(tbl), 
        to_table = Table.FromColumns(
            List.FirstN(cols, 3) & 
            List.Transform(
                List.RemoveFirstN(cols, 3),
                (x) => List.Skip(x, (w) => w is null)
            ),
            Table.ColumnNames(tbl)
        )][to_table],
    group = Table.Group(Source, {"Day", "Date", "Operator"}, {{"x", func}}),
    result = Table.Combine(group[x])
in
    result

[dufoq3 · Accepted solution]

Hi @Andresgamez28, similar approach here:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3sNA3MjAyUdJRck4sygFSxmZmRuZAGopidbCqAiIzKwMDIFJw9CWoFEl1gC9BpUBkaKhnClZkhN2BFqgOxKIKlwOxK8XqQJxK4Q6MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operator = _t, #"Docket No" = _t, #"Start Time" = _t, #"Finish Time" = _t, Hours = _t]),
    // You can delete this step when applying on real data.
    RepalceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    GroupedRows = Table.Group(RepalceBlankToNull, {"Date", "Operator"}, {{"All", each Table.FromColumns(List.Transform(Table.ToColumns(_), each List.FirstN(List.RemoveNulls(_), 1)), Value.Type(_)), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[Omid_Motamedise · Accepted solution]

You can use this formula (Source is your data )

let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],

List={"Day", "Date", "Operator"},
Unpivoted = Table.UnpivotOtherColumns(Source, List, "Attribute", "Value"),
Pivoted = Table.Pivot(Unpivoted, List.Distinct(Unpivoted[Attribute]), "Attribute", "Value", List.First),
Custom1 = Table.Sort( Pivoted & Table.Repeat(Table.SelectColumns(Pivoted,List),4), "Date")
in
Custom1

[dufoq3 · Accepted solution]

Hi @Andresgamez28, similar approach here:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3sNA3MjAyUdJRck4sygFSxmZmRuZAGopidbCqAiIzKwMDIFJw9CWoFEl1gC9BpUBkaKhnClZkhN2BFqgOxKIKlwOxK8XqQJxK4Q6MBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Operator = _t, #"Docket No" = _t, #"Start Time" = _t, #"Finish Time" = _t, Hours = _t]),
    // You can delete this step when applying on real data.
    RepalceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    GroupedRows = Table.Group(RepalceBlankToNull, {"Date", "Operator"}, {{"All", each Table.FromColumns(List.Transform(Table.ToColumns(_), each List.FirstN(List.RemoveNulls(_), 1)), Value.Type(_)), type table}}),
    Combined = Table.Combine(GroupedRows[All])
in
    Combined

[HishamHAMDY]

amazing

[AlienSx · Accepted solution]

let
    Source = your_table,
    func = (tbl) => 
        [cols = Table.ToColumns(tbl), 
        to_table = Table.FromColumns(
            List.FirstN(cols, 3) & 
            List.Transform(
                List.RemoveFirstN(cols, 3),
                (x) => List.Skip(x, (w) => w is null)
            ),
            Table.ColumnNames(tbl)
        )][to_table],
    group = Table.Group(Source, {"Day", "Date", "Operator"}, {{"x", func}}),
    result = Table.Combine(group[x])
in
    result

[lbendlin]

What should happen when you have more than or less than one value per column ?