Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
= Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index1", {"Index"}, "Index1", JoinKind.LeftOuter)
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:
Select ‘All Rows’ in the Operation.
= Table.NestedJoin(#"Expanded Custom", {"Index","Group"}, #"Expanded Custom1", {"Index","Group"}, "Added Index1", JoinKind.LeftOuter)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.