PowerQuery Transformation

[Anonymous]

Hi All

I would like to know if this transformation is possible in PowerQuery or DAX.

[FrankAT]

Hi @Anonymous 

you can do it with Power Query like this:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRU0lGKhONYHdxCflAhP7CQkxNUyI+gUCRcM0jI2ZmgUCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Col 1" = _t, #"Col 2" = _t, #"Col 3" = _t, #"Col 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col 1", type text}, {"Col 2", type text}, {"Col 3", type text}, {"Col 4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Col 1"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Col 1", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Value]), "Value", "Count", List.Sum)
in
    #"Pivoted Column"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[nandic · Accepted solution]

You can make it in Power Query:
1) select first column (col 1) > transform > unpivot columns > unpivot other columns
2) group by > advanced > group by col 1, value > aggregate default (count rows)

Go to Power BI desktop:
1) create crosstab: col 1 in rows, value in columns, count in values.
* Note: you can rename these columns as you wish.