Changing context of column reference in each statement

[pelowski]

What I would like to do here is dynamically reference a column name in a previous PQ step from within an each statement.  Does this require an Expression.Evaluate operation?  How can I refer to a changing column name in this context?

Example M Code

let
	Data = #table(
	type table
		[
			#"League"=text,
			#"Team"=text,
			#"Conference"=text,
			#"Division"=text
		],
	{
		{"NBA", "Boston Celtics", "Eastern", "Atlantic"},
		{"NBA", "Brooklyn Nets", "Eastern", "Atlantic"},
		{"NBA", "New York Knicks", "Eastern", "Atlantic"},
		{"NBA", "Philadelphia 76ers", "Eastern", "Atlantic"},
		{"NBA", "Toronto Raptors", "Eastern", "Atlantic"},
		{"NBA", "Chicago Bulls", "Eastern", "Central"},
		{"NBA", "Cleveland Cavaliers", "Eastern", "Central"},
		{"NBA", "Detroit Pistons", "Eastern", "Central"},
		{"NBA", "Indiana Pacers", "Eastern", "Central"},
		{"NBA", "Milwaukee Bucks", "Eastern", "Central"},
		{"NBA", "Atlanta Hawks", "Eastern", "Southeast"},
		{"NBA", "Charlotte Hornets", "Eastern", "Southeast"},
		{"NBA", "Miami Heat", "Eastern", "Southeast"},
		{"NBA", "Orlando Magic", "Eastern", "Southeast"},
		{"NBA", "Washington Wizards", "Eastern", "Southeast"},
		{"NBA", "Denver Nuggets", "Western", "Northwest"},
		{"NBA", "Minnesota Timberwolves", "Western", "Northwest"},
		{"NBA", "Oklahoma City Thunder", "Western", "Northwest"},
		{"NBA", "Portland Trail Blazers", "Western", "Northwest"},
		{"NBA", "Utah Jazz", "Western", "Northwest"},
		{"NBA", "Golden State Warriors", "Western", "Pacific"},
		{"NBA", "Los Angeles Clippers", "Western", "Pacific"},
		{"NBA", "Los Angeles Lakers", "Western", "Pacific"},
		{"NBA", "Phoenix Suns", "Western", "Pacific"},
		{"NBA", "Sacramento Kings", "Western", "Pacific"},
		{"NBA", "Dallas Mavericks", "Western", "Southwest"},
		{"NBA", "Houston Rockets", "Western", "Southwest"},
		{"NBA", "Memphis Grizzlies", "Western", "Southwest"},
		{"NBA", "New Orleans Pelicans", "Western", "Southwest"},
		{"NBA", "San Antonio Spurs", "Western", "Southwest"},
		{"NFL", "Buffalo Bills", "AFC", "East"},
		{"NFL", "Miami Dolphins", "AFC", "East"},
		{"NFL", "New England Patriots", "AFC", "East"},
		{"NFL", "New York Jets", "AFC", "East"},
		{"NFL", "Baltimore Ravens", "AFC", "North"},
		{"NFL", "Cincinnati Bengals", "AFC", "North"},
		{"NFL", "Cleveland Browns", "AFC", "North"},
		{"NFL", "Pittsburgh Steelers", "AFC", "North"},
		{"NFL", "Houston Texans", "AFC", "South"},
		{"NFL", "Indianapolis Colts", "AFC", "South"},
		{"NFL", "Jacksonville Jaguars", "AFC", "South"},
		{"NFL", "Tennessee Titans", "AFC", "South"},
		{"NFL", "Denver Broncos", "AFC", "West"},
		{"NFL", "Kansas City Chiefs", "AFC", "West"},
		{"NFL", "Las Vegas Raiders", "AFC", "West"},
		{"NFL", "Los Angeles Chargers", "AFC", "West"},
		{"NFL", "Dallas Cowboys", "NFC", "East"},
		{"NFL", "New York Giants", "NFC", "East"},
		{"NFL", "Philadelphia Eagles", "NFC", "East"},
		{"NFL", "Washington Redskins", "NFC", "East"},
		{"NFL", "Chicago Bears", "NFC", "North"},
		{"NFL", "Detroit Lions", "NFC", "North"},
		{"NFL", "Green Bay Packers", "NFC", "North"},
		{"NFL", "Minnesota Vikings", "NFC", "North"},
		{"NFL", "Atlanta Falcons", "NFC", "South"},
		{"NFL", "Carolina Panthers", "NFC", "South"},
		{"NFL", "New Orleans Saints", "NFC", "South"},
		{"NFL", "Tampa Bay Buccaneers", "NFC", "South"},
		{"NFL", "Arizona Cardinals", "NFC", "West"},
		{"NFL", "Los Angeles Rams", "NFC", "West"},
		{"NFL", "San Francisco 49ers", "NFC", "West"},
		{"NFL", "Seattle Seahawks", "NFC", "West"},
		{"NHL", "Boston Bruins", "Eastern", "Atlantic"},
		{"NHL", "Buffalo Sabres", "Eastern", "Atlantic"},
		{"NHL", "Detroit Red Wings", "Eastern", "Atlantic"},
		{"NHL", "Florida Panthers", "Eastern", "Atlantic"},
		{"NHL", "Montreal Canadiens", "Eastern", "Atlantic"},
		{"NHL", "Ottawa Senators", "Eastern", "Atlantic"},
		{"NHL", "Tampa Bay Lightning", "Eastern", "Atlantic"},
		{"NHL", "Carolina Hurricanes", "Eastern", "Metropolitan"},
		{"NHL", "Columbus Blue Jackets", "Eastern", "Metropolitan"},
		{"NHL", "New Jersey Devils", "Eastern", "Metropolitan"},
		{"NHL", "New York Islanders", "Eastern", "Metropolitan"},
		{"NHL", "New York Rangers", "Eastern", "Metropolitan"},
		{"NHL", "Philadelphia Flyers", "Eastern", "Metropolitan"},
		{"NHL", "Pittsburgh Penguins", "Eastern", "Metropolitan"},
		{"NHL", "Toronto Maple Leafs", "Eastern", "Metropolitan"},
		{"NHL", "Washington Capitals", "Eastern", "Metropolitan"},
		{"NHL", "Chicago Blackhawks", "Western", "Central"},
		{"NHL", "Colorado Avalanche", "Western", "Central"},
		{"NHL", "Dallas Stars", "Western", "Central"},
		{"NHL", "Minnesota Wild", "Western", "Central"},
		{"NHL", "Nashville Predators", "Western", "Central"},
		{"NHL", "St Louis Blues", "Western", "Central"},
		{"NHL", "Winnipeg Jets", "Western", "Central"},
		{"NHL", "Anaheim Ducks", "Western", "Pacific"},
		{"NHL", "Arizona Coyotes", "Western", "Pacific"},
		{"NHL", "Calgary Flames", "Western", "Pacific"},
		{"NHL", "Edmonton Oilers", "Western", "Pacific"},
		{"NHL", "Los Angeles Kings", "Western", "Pacific"},
		{"NHL", "San Jose Sharks", "Western", "Pacific"},
		{"NHL", "Vancouver Canucks", "Western", "Pacific"},
		{"NHL", "Vegas Golden Knights", "Western", "Pacific"}
	}),
    ProfileData = Table.Profile(Data),
    #"Removed Other Columns" = Table.SelectColumns(ProfileData,{"Column", "DistinctCount"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "Unique Values", each if [DistinctCount] <= 5 then List.Sort(List.Distinct(Data[Column])) else null, type list)
in
	#"Added Custom"

[parry2k]

@pelowski what is your end goal?

[pelowski]

The end goal for this example is a distinct/sorted list of the values in the Conference and League columns (in this example) from the Data step because they match the criteria in the "Added Custom" step by having a distinct count that is less than or equal to 5.  I would expect the values to equal {"AFC", "Eastern", "NFC", "Western"} and {"NBA", "NFL", "NHL"} respectively.

The overall end goal (irrespectively of this simple example) is to understand how to dynamically reference a column name from a previous step within an each statement like this.

[pelowski]

In the usage case I hope this will solve, I'm adding some custom columns using Lars Schreiber's article about the second parameter of Table.Profile and I'm using it in a custom function to get a better understanding of any table I pass to the custom function.  One of the columns I'd like to create is a list of distinct values for any given text or numeric column that has a limited number of distinct values.

[HotChilli · Accepted solution]

Put

Table.Column(Data, [Column])

in the brackets for the List.Distinct

It's quite powerful but difficult to find.

[pelowski]

Wow!  It was as easy as that!  Thanks!

FWIW, I swear I've been over that documentation hundreds of times but I think this is the first time I've used Table.Column!  🙄  Always learning something new!  Thanks again!