Counting a table by groups an item is within

[Guitarmageddon]

Hey folks, I know this is simple and probably related to something with grouping. Ive got a table that is a query result of TABLE names and their COLUMN names within. There are duplicated column names that appear in different tables (many repetitions across tables where these columns are reused).

I'm trying to write a measure that's just showing how many distinct table names you will find that column name within.

For example, you can see that "business unit ID" is found in the three distinct table names shown below. In the example below, the measure below would show a "3" wherever it saw column name "business unit ID"

 

[alansidman]

Cannot manipulate data in a picture. Don't want to have to retype all your data that you already have. Please repost using XL2BB so that we can test solutions before publishing.

 

[Guitarmageddon]

Cannot manipulate data in a picture. Don't want to have to retype all your data that you already have. Please repost using XL2BB so that we can test solutions before publishing.

TABLE_NAME	COLUMN_NAME
ACTIVE_PHYSICAL_INV_STYLES	BUSINESS_UNIT_ID
ACTIVE_PHYSICAL_INV_STYLES	STYLE_ID
ACTIVE_PHYSICAL_INV_STYLES	DOCUMENT_ID
ADD_INFO_DATA	DATA_SEQUENCE_ID
ADD_INFO_DATA	ADD_INFO_DATA
ADD_INFO_DATA	ADD_INFO_KEY_5_DATA
ADD_INFO_DATA	ADD_INFO_KEY_4_DATA
ADD_INFO_DATA	ADD_INFO_KEY_3_DATA
ADD_INFO_DATA	ADD_INFO_KEY_2_DATA
ADD_INFO_DATA	ADD_INFO_KEY_1_DATA
ADD_INFO_DATA	ADD_INFO_ID
ADD_INFO_DATA	ADD_INFO_TABLE_NAME
ADD_INFO_DATA	BUSINESS_UNIT_ID
ADD_INFO_DATA_ACTIVITY	PROCESSED_DATE
ADD_INFO_DATA_ACTIVITY	STATUS
ADD_INFO_DATA_ACTIVITY	CREATION_DATE
ADD_INFO_DATA_ACTIVITY	ACTIVITY_TYPE
ADD_INFO_DATA_ACTIVITY	DATA_SEQUENCE_ID
ADD_INFO_DATA_ACTIVITY	BUSINESS_UNIT_ID
ADD_INFO_DATA_ACTIVITY	USERNAME

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"COLUMN_NAME"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}})
in
    #"Grouped Rows"

Book4
	A	B	C	D	E
1	TABLE_NAME	COLUMN_NAME		COLUMN_NAME	Count
2	ACTIVE_PHYSICAL_INV_STYLES	BUSINESS_UNIT_ID		BUSINESS_UNIT_ID	3
3	ACTIVE_PHYSICAL_INV_STYLES	STYLE_ID		STYLE_ID	1
4	ACTIVE_PHYSICAL_INV_STYLES	DOCUMENT_ID		DOCUMENT_ID	1
5	ADD_INFO_DATA	DATA_SEQUENCE_ID		DATA_SEQUENCE_ID	2
6	ADD_INFO_DATA	ADD_INFO_DATA		ADD_INFO_DATA	1
7	ADD_INFO_DATA	ADD_INFO_KEY_5_DATA		ADD_INFO_KEY_5_DATA	1
8	ADD_INFO_DATA	ADD_INFO_KEY_4_DATA		ADD_INFO_KEY_4_DATA	1
9	ADD_INFO_DATA	ADD_INFO_KEY_3_DATA		ADD_INFO_KEY_3_DATA	1
10	ADD_INFO_DATA	ADD_INFO_KEY_2_DATA		ADD_INFO_KEY_2_DATA	1
11	ADD_INFO_DATA	ADD_INFO_KEY_1_DATA		ADD_INFO_KEY_1_DATA	1
12	ADD_INFO_DATA	ADD_INFO_ID		ADD_INFO_ID	1
13	ADD_INFO_DATA	ADD_INFO_TABLE_NAME		ADD_INFO_TABLE_NAME	1
14	ADD_INFO_DATA	BUSINESS_UNIT_ID		PROCESSED_DATE	1
15	ADD_INFO_DATA_ACTIVITY	PROCESSED_DATE		STATUS	1
16	ADD_INFO_DATA_ACTIVITY	STATUS		CREATION_DATE	1
17	ADD_INFO_DATA_ACTIVITY	CREATION_DATE		ACTIVITY_TYPE	1
18	ADD_INFO_DATA_ACTIVITY	ACTIVITY_TYPE		USERNAME	1
19	ADD_INFO_DATA_ACTIVITY	DATA_SEQUENCE_ID
20	ADD_INFO_DATA_ACTIVITY	BUSINESS_UNIT_ID
21	ADD_INFO_DATA_ACTIVITY	USERNAME
Sheet1