Count number of columns measure

[Anonymous]

Hi, 

is there any way to count the number of columns in a table? (to be displayed in a visual) 

I don't see a function that would do this as a measure. 

My other idea would be to transpose the table and count rows, but I don't see a function for that either.

Thanks,

Ruben

[Icey]

Hi @Anonymous ,

Try this:

1. In Power Query Editor, Duplicate your table.

2. Select all columns and UnPivot.

3. Remove 'Value' column.

4. Remove duplicate rows.

5. Create a measure.

Count of Columns = COUNTROWS ( 'Table (2)' )

6. Hidden 'Table (2)'.

Best Regards,

Icey

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

[jdbuchanan71 · Accepted solution]

You can do it in PowerQuery.  If you reference your first query as the source you can keep only the first row then unpivot all the columns.  In my example I have a table called "data"

let
    Source = data,
    #"Kept First Rows" = Table.FirstN(Source,1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Kept First Rows", {}, "Attribute", "Value")
in
    #"Unpivoted Columns"

Just make sure to select all the columns before you unpivot.

Then you feed this to the model and do the count off the new table.  You could even remove the value column before loading it.

[kentyler]

You and use the COUNTROWS() function to count rows once you transpose your table

[Anonymous]

@kentyler 

How do I transpose the table?

Kindest regards,

Ruben