Move Columns to rows, get count, max, min

[Anonymous]

Hi there, I am new to Power BI and I am stuck trying a matrix. I need to move this columns so they can all be placed into one column, and then I can get the values from it.  My problem is something like this..

addr_group        address_id          add_number     add_origin          building             checkstatus

xxxxxxxxxxxx      xxxxxxxxxxx        xxxxxxxxxxxxx    xxxxxxxxxxx        xxxxxxx                xxxxxxxxxxxx

xxxxxxxxxxxx      xxxxxxxxxxx        xxxxxxxxxxxxx    xxxxxxxxxxx        xxxxxxx                xxxxxxxxxxxx

xxxxxxxxxxxx      xxxxxxxxxxx        xxxxxxxxxxxxx    xxxxxxxxxxx        xxxxxxx                xxxxxxxxxxxx

xxxxxxxxxxxx      xxxxxxxxxxx        xxxxxxxxxxxxx    xxxxxxxxxxx        xxxxxxx                xxxxxxxxxxxx

Into >>>

Column                Count                    DistinctCount     Max       Min        NullCount

Addr_group

Address_id

Add_number

Add_origin

Building

Checkstatus

 Thx for your help

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

First, Unpivot your data in power Query:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("HYy5DQAxDMN2cZ3ipCT3zGJ4/zXOTCHCsARmhmLE7nwdTfA0rKiR4T5vfhdY4KX1aVkzFg4hERbP07JmLCTCYixeUfUD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [addr_group = _t, address_id = _t, add_number = _t, add_origin = _t, building = _t, checkstatus = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"addr_group", Int64.Type}, {"address_id", Int64.Type}, {"add_number", Int64.Type}, {"add_origin", Int64.Type}, {"building", Int64.Type}, {"checkstatus", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Then apply it to the matrix:

Here is the calculation of NullCount:

NullCount = CALCULATE(COUNT('Table'[Value]),FILTER('Table',[Value]=BLANK()))

(Other Aggregation can use directly)

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[JorgePinho]

Put those fields in the Rows Box

Then in the Values Box put the measures that you want to see (Count, Distinct Count...)