Power Query rank strange behaviour

[AnetaK]

Hi,

I added a column with rank in PQ (using Table.AddIndexColumn function), based on the value of a date .

Then I added this value to a different table (merge).

When I look into the data I see it shows good values.

But when I expand this column then the rank numbers are different.

Anyone knows why?

[AnetaK]

I created an example.

This is a table, where I added a rank.

Null is #1.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AMiIwMjI6VYHaiQsR4QoQqZ6gERihCYkYRpQhKaCbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Date", type date}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Category", Order.Ascending}, {"Date", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Category"}, {{"Count", each _, type table [Category=nullable text, Date=nullable date]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Rank",1,1)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Category", "Count"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Category", "Date", "Rank"}, {"Category", "Date", "Rank"})
in
#"Expanded Custom"

Then I added this column by merge to another table, and null is #4.

Weird.

But one step earlier it is #1.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIw1AMiIwMjI6VYHaiQsR4QoQqZ6gERihCYkYRpQhKaCbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}, {"Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Category", "Date"}, #"Table with rank", {"Category", "Date"}, "Table (2)", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table (2)", {"Rank"}, {"Rank"})
in
#"Expanded Table"

[v-xiaotang]

Hi @AnetaK 

Thanks for reaching out to us.

>> Then I added this column by merge to another table, and null is #4.  ...  But one step earlier it is #1.

I create a sample file, the rank of the null line is always 1,

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[AnetaK]

Hm?

[AnetaK]

Did you use my M code?