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 un...

Need Help

Show and Tell

PaulDBrown
4 years ago
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

v-henryk-mstf

Community Support

4 years ago

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"