Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thanks for your help, Works exactly as expected. 😊
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.
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |