Forum Discussion

abelrmg's avatar
abelrmg
Icon for Helper II rankHelper II
6 years ago

HELP INDEX COLUMN WITH RANGES

I have a two columns "JOB" and "ROL", Job is a incremental number and "ROL" this could be "A","B" or "C". And I would like to add a column like index considering how many equals roles there are for e...
Need Help
Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
6 years ago

Hi abelrmg 

 

You can try something like below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitVBZznBWc5glhFc1gguawSXNYbLGsNlcbGAOmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [JOB = _t, ROL = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOB", Int64.Type}, {"ROL", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JOB", "ROL"}, {{"Index", each Table.AddIndexColumn( _, "Index", 1 )[Index], type list }}),
    #"Expanded Index" = Table.ExpandListColumn(#"Grouped Rows", "Index"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Index",{{"Index", Int64.Type}})
in
    #"Changed Type1"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.