Create a new column based on distinct Columns

[Anonymous]

My basic idea is to bring up a line chart by grouping by multiple columns. Below I have given a simple table.

Time_mins	Result_type	Stability_time	Analysis_type	Avg - Dissolved Uncorrected
10	Individual		manual	56.15714286
5	Individual		manual	46.91190476
10	Individual		fully automated	72.45590551
5	Individual		fully automated	58.72992126
10	Individual	4 week	fully automated	88.65853659
5	Individual	4 week	fully automated	83.52439024
8	Individual		semi-automated	79.18888889

Now I need to create a index column based on distinct values of Stability_time and Analysis_type, just like below.

Time_mins	Result_type	Stability_time	Analysis_type	Avg - Dissolved Uncorrected	Custom
10	Individual		manual	56.15714286	1
5	Individual		manual	46.91190476	1
10	Individual		fully automated	72.45590551	2
5	Individual		fully automated	58.72992126	2
10	Individual	4 week	fully automated	88.65853659	3
5	Individual	4 week	fully automated	83.52439024	3
8	Individual		semi-automated	79.18888889	4

Please help me here. Thanks in Advance.

[Fowmy · Accepted solution]

@Anonymous 

Paste below code ina blank query in the ADvanced Editor and follow steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BCoMwDIZfRXp2oYlJ2zzCnkE8FOxApu4w3djbr+hprBrI4Q//ny9pW4PW1OY698Nr6Nc4ZlHlnuK8C3GA4pEpONPVrZFTNztQRLXsd3d5+W0dx08V1+UxxSX1eeIJWEStCB5T/mMSwJMqIR3huHqndC+GQwAnQRonWmaeZRsQ4kYt8ZYNpXufaRouP18qYNgqE7sv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Time_mins = _t, Result_type = _t, Stability_time = _t, Analysis_type = _t, #"Avg - Dissolved Uncorrected" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time_mins", Int64.Type}, {"Avg - Dissolved Uncorrected", type number}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({[Stability_time], [Analysis_type]}, "|"), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"Merged"}, {{"Count", each _, type table [Time_mins=nullable number, Result_type=nullable text, Stability_time=nullable text, Analysis_type=nullable text, #"Avg - Dissolved Uncorrected"=nullable number, Merged=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Merged"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"Time_mins", "Result_type", "Stability_time", "Analysis_type", "Avg - Dissolved Uncorrected"}, {"Time_mins", "Result_type", "Stability_time", "Analysis_type", "Avg - Dissolved Uncorrected"})
in
    #"Expanded Count"

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Thanks for your help, Works exactly as expected. 😊 

[vanessafvg]

in power query you can duplicate the data set, then do a group by on the relevant columns, and then add an identity column. like this, see attached.

[Anonymous]

Thanks for the reply, But I need it exactly as the table I mentioned above. From the example you gave the table again got shrunk on grouping by rows, which will not be helpful for my manipulations.