Transform Multiple Column to Multiple Rows and Count Rows

[Anonymous]

Hi,

I currently have this data:

And I wish to have the following results:

How can I create this using the "New Table" function? I might also create a new excel sheet with the "Categories". How then can I utilize DAX to Count the rows for multiple columns?

Thanks in advanced!

Regards,

Nicholas Hiew

[Zubair_Muhammad · Accepted solution]

@Anonymous

Using Power Query/Query Editor would be much more efficient

Nevertheless here is a DAX calculated table that might work

New Table =
VAR A =
    SUMMARIZECOLUMNS (
        "Category", "A",
        "Count", CALCULATE ( COUNT ( Table1[A] ), Table1[A] = "A" )
    )
VAR B =
    SUMMARIZECOLUMNS (
        "Category", "B",
        "Count", CALCULATE ( COUNT ( Table1[B] ), Table1[B] = "B" )
    )
VAR E =
    SUMMARIZECOLUMNS (
        "Category", "E",
        "Count", CALCULATE ( COUNT ( Table1[E] ), Table1[E] = "E" )
    )
RETURN
    UNION ( A, B, E )

[Anonymous]

Thanks! This works!

I will try the power query solution too.

[Zubair_Muhammad]

@Anonymous

Using Query Editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlEwVNJRcgRiJyB2BmIQitVByBtBxGDSLujyxlD9uORNUPWjS5uiagciVxR5M6gehOmo8uYI47HKWyDMx2K7JZLnsTne0ABVAcT4WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Project = _t, A = _t, B = _t, C = _t, D = _t, E = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"A", type text}, {"B", type text}, {"C", type text}, {"D", type text}, {"E", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Project"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Value] <> ""))
in
    #"Filtered Rows"