Create a new column based on distinct Columns

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.

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

4 Replies

vanessafvg
Community Champion
6 years ago
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.
identity.pbix24 KB
- Anonymous
  Not applicable
  6 years ago
  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.

Fowmy

Super User

6 years ago

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
Not applicable
6 years ago
Thanks for your help, Works exactly as expected. 😊

Forum Discussion

Create a new column based on distinct Columns

4 Replies

Related content

Distinct count of users via Created By column

Create columns group

Distinctive Count across two columns

count distinct

Create New Table Based on DIstinct Values of One Column and Calculated Values of Another Column