Move Columns to rows, get count, max, min

Question

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.&nbsp; My problem is something like this..&nbsp;addr_group&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; address_id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; add_number&nbsp;&nbsp;&nbsp;&nbsp; add_origin&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; building&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;checkstatusxxxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxx&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxxxxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxx&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxxxxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxx&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxxxxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxxx&nbsp;&nbsp;&nbsp; xxxxxxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxx&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; xxxxxxxxxxxx&nbsp;&nbsp;Into &gt;&gt;&gt;&nbsp;Column&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Count&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; DistinctCount&nbsp;&nbsp;&nbsp;&nbsp; Max&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Min&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NullCountAddr_groupAddress_idAdd_numberAdd_originBuildingCheckstatus&nbsp;&nbsp;&nbsp;Thx for your help

v-jianboli-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
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&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;
&nbsp;
&nbsp;
&nbsp;

Forum Discussion

Move Columns to rows, get count, max, min

2 Replies

Related content

Total Count in Column

Unique Count in Row convert

Column to Row

Grouping rows by Column and then updating Column

Count rows with condition