Forum Discussion

fedpar's avatar
fedpar
Microsoft Employee
10 years ago
Solved

Custom column Index or Ranking by other column

I'm looking to add an index column, but have it increase according to a certain column value. Let me give an example; let's say my data is: Group      Date A 18-Apr A 19-Apr A 23-Apr...
  • ImkeF's avatar
    ImkeF
    10 years ago

    Thats like an index on a table partition. You can create that by using grouping on the column and returning "_" - which means that all column of the table (but only for the specific value in the column) will be return. You then nest your Index-command in:

     

    let
    Source = Table1,
    Partition = Table.Group(Source, {"Group"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Partition"
ImkeF's avatar
ImkeF
Community Champion
10 years ago

You're welcome. Please check out this video:

 

 

 

and let me know if you need further help on this.

mbuick's avatar
mbuick
Frequent Visitor
8 years ago

HI,

 

I've just found that after grouping and then expanding the group to continue using all fields in the table, the field types are all changed to alpha numeric, hence rendering all the graphs and charts unoperable. Is there a way to bulk return all the field types to their origin types or negate this effect in the grouping?

 

thanks.

  • ImkeF's avatar
    ImkeF
    Community Champion
    8 years ago

    Yes, thats a bit of a pain that I've just realized recently. You can avoid it by using Table.Combine instead of expanding the column like this:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00DMw0TMyMDRXitXBKWRkjCFkYKhnYIoQcgKpMkRRhUPI2ACvUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Group = _t, Date = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "de-DE"),
    Partition = Table.Group(#"Changed Type with Locale", {"Group"}, {{"Partition", each Table.AddIndexColumn(Table.Sort(_,{{"Date", Order.Ascending}}), "Index",1,1), type table}}),
    Table.Combine = Table.Combine(Partition[Partition])
in
    Table.Combine