Skip null values to add index columns


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:   

RicoZhou_0-1658216739847.png

 

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.

RicoZhou_1-1658216739848.png

 

RicoZhou_2-1658216739850.png

 

RicoZhou_3-1658216739851.png

 

  1. Filter out the null values.

RicoZhou_4-1658216739852.png

 

RicoZhou_5-1658216739853.png

 

  1. Add an index column again.

RicoZhou_6-1658216739854.png

 

RicoZhou_7-1658216739855.png

 

  1. 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)

 

 

 

RicoZhou_8-1658216739856.png

 

RicoZhou_9-1658216739856.png

 

RicoZhou_10-1658216739858.png

 

  1. 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.

RicoZhou_11-1658216739859.png

 

RicoZhou_12-1658216739860.png

 

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

RicoZhou_13-1658216739861.png

 

RicoZhou_14-1658216739862.png

 

RicoZhou_15-1658216739863.png

 

RicoZhou_16-1658216739863.png

 

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: 

RicoZhou_17-1658216739864.png

 

Please see the solution below.

 

Solution:

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

RicoZhou_18-1658216739866.png

 

Select ‘All Rows’ in the Operation.

RicoZhou_19-1658216739866.png

 

RicoZhou_20-1658216739867.png

 

  1. Add an index column.

RicoZhou_21-1658216739868.png

 

RicoZhou_22-1658216739869.png

 

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

RicoZhou_23-1658216739869.png

 

RicoZhou_24-1658216739870.png

 

RicoZhou_25-1658216739870.png

 

RicoZhou_26-1658216739871.png

 

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

RicoZhou_27-1658216739872.png

 

RicoZhou_28-1658216739873.png

 

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

RicoZhou_29-1658216739874.png

 

RicoZhou_30-1658216739875.png

 

RicoZhou_31-1658216739875.png

 

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

RicoZhou_32-1658216739876.png

 

RicoZhou_33-1658216739877.png

 

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

RicoZhou_34-1658216739878.png

 

RicoZhou_35-1658216739878.png

 

RicoZhou_36-1658216739879.png

 

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

 

 

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

 

 

 

RicoZhou_37-1658216739881.png

 

RicoZhou_38-1658216739882.png

 

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

RicoZhou_39-1658216739883.png

 

RicoZhou_40-1658216739884.png

 

RicoZhou_41-1658216739886.png

 

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



Source link

Be the first to comment

Leave a Reply

Your email address will not be published.


*