Skip null values to add index columns

[v-rzhou-msft]

Scenario:   

It's easy to add a normal index column, but what if there are nulls in the row, the index column is added and the nulls remain? For some special cases, in addition to a column with null values, there are other columns in the table, so we need to keep null rows when adding the index column. An example is shown in the figure below.

Example:   

In this article, we will talk about how to skip null values to add index columns.

Solution: 

1. Add an index column in the table.

2. Filter out the null values.

3. Add an index column again.

4. In the applied steps, insert a new step after the last step. And then rename it as ‘Merged Queries’, add the following formula in it. This formula is to merge the previous table (Table in the step Added Index) with the current table (Table in the step Added Index1).

= Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Index1", JoinKind.LeftOuter)

5. You can see that the row with no null value corresponds to the Date column, Index column and Index1 column in the table, and the table corresponding to the row with null value is an empty table.

6. Remove the Index column, and then expand the Index1 column in the tables in the Added Index1 column.

The whole M syntax:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI11DVUitWJVsorzckBM8CiRlBRFA6qEmMcokbIHBNkjqlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),

    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),

    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([Date] <> null)),

    #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows", "Index1", 1, 1, Int64.Type),

    #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Added Index1", JoinKind.LeftOuter),

    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Index"}),

    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Columns", "Added Index1", {"Index1"}, {"Index1"})

in

    #"Expanded Added Index1"

Extension: 

According to the above content, we have learned how to skip null values to add an index column, so if there is still a column of grouping in the table, we need to group them and add index columns, what should we do?

Example: 

Please see the solution below.

Solution:

1. Select the Group column, then click ‘Group By...’.

Select ‘All Rows’ in the Operation.

2. Add an index column.

3. Remove the Count column and expand the Date column and the Index column.

4. Filter out the null values in the Date column.

5. Select the Group column and then group by again.

6. Add an index column based on the Count column.

7. Remove the Count column and expand the Date column, the Index column and the Index1 column in the tables in the Custom Column.

8. Same as above, add a step, enter the formula.

= Table.NestedJoin(#"Expanded Custom", {"Index","Group"}, #"Expanded Custom1", {"Index","Group"}, "Added Index1", JoinKind.LeftOuter)

9. Expand the Index1 column, remove the unneeded column.

The whole M syntax:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI11DVU0lFyVIrViVbKK83JgXPAskZIshgCmMqNCcgaoQuYoAuYogpAXOeEbKATumNwCGAqNyYga4QuYIIuYAoRiAUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Group = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Count", each _, type table [Date=nullable date, Group=nullable text]}}),

    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),

    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Date", "Index"}, {"Date", "Index"}),

    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Date] <> null)),

    #"Grouped Rows1" = Table.Group(#"Filtered Rows", {"Group"}, {{"Count", each _, type table [Group=nullable text, Date=date, Index=number]}}),

    #"Added Custom1" = Table.AddColumn(#"Grouped Rows1", "Custom", each Table.AddIndexColumn([Count],"Index1",1,1)),

    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Count"}),

    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Date", "Index", "Index1"}, {"Date", "Index", "Index1"}),

    #"Merged Queries" = Table.NestedJoin(#"Expanded Custom", {"Index","Group"}, #"Expanded Custom1", {"Index","Group"}, "Added Index1", JoinKind.LeftOuter),

    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Index1"}, {"Index1"}),

    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Added Index1",{"Index"})

in

    #"Removed Columns2"

Hope this article helps everyone with similar questions.  

Author: Stephen Tao

Reviewer: Kerry Wang & Ula Huang

Skip null values to add index columns.pbix