Forum Discussion

AnetaK's avatar
AnetaK
Helper V
3 years ago

Power Query rank strange behaviour

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?

Need Help

4 Replies

  • AnetaK's avatar
    AnetaK
    Helper V
    3 years ago

    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's avatar
      v-xiaotang
      Community Support
      3 years ago

      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.