Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pelowski
Helper III
Helper III

Changing context of column reference in each statement

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?

 

PQ - Dynamic Column Reference.png

 

 

 

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"

 

1 ACCEPTED SOLUTION

Put

Table.Column(Data, [Column])

in the brackets for the List.Distinct

 

It's quite powerful but difficult to find.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@pelowski what is your end goal?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

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.

Put

Table.Column(Data, [Column])

in the brackets for the List.Distinct

 

It's quite powerful but difficult to find.

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.