Forum Discussion
Pivoting table?
Does anyone know how to count unique values in both of these columns, and create a table where the columns are the name of the unique values, and the rows are the names with attached number of the unique values?
I did the example using the measure function but I need to code every possible column name and it isn't robust with bigger data.
Current TableThe Desired Outcome
With the original data table, create a matrix visual using Operator name field as rows, PlatformType field as column and add this measure to the Values bucket in the matrix:
DistinctCount of PlatformType = DISTINCTCOUNT (Current Table [PlatformType])
BTW your desired output example doesn't match the original table data. If you want the number of rows for each PlatformType per user use COUNT instead of DISTINCTCOUNT
4 Replies
With the original data table, create a matrix visual using Operator name field as rows, PlatformType field as column and add this measure to the Values bucket in the matrix:
DistinctCount of PlatformType = DISTINCTCOUNT (Current Table [PlatformType])
BTW your desired output example doesn't match the original table data. If you want the number of rows for each PlatformType per user use COUNT instead of DISTINCTCOUNT
It works great! Cheers. Yeah, you're right I cut off the last row, it should be one more with TQL.
Hi maciekbala ,
I have done tests and think I can achieve this in power query. Refer to the following steps:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQoJ9FGK1YlWckJiOwPZfqn5CgbGmRFgARdsAmDVsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OperatorName = _t, PlatformType = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"OperatorName", type text}, {"PlatformType", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OperatorName"}, {{"Data", each _, type table [OperatorName=nullable text, PlatformType=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TQL", each Table.RowCount(Table.SelectRows([Data], each [PlatformType]= "TQL")), Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Neo", each Table.RowCount(Table.SelectRows([Data], each [PlatformType]= "Neo 03iX")), Int64.Type), #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Data"}) in #"Removed Columns"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you, yes it works but as you can see you manually define and create new columns. In my case I need to create them automatically.
