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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bdue
Helper I
Helper I

How to adjust grouped items by another field

One of the primary columns in my data set is "location," but one of my locations needs to be broken out into three separate locations.  I have data in another column that I can use to do this (based upon the "department" field).  If I could change the source data I would.

 

Of course, I can use slicers to filter to the correct data, but I want to use edit query (or a measure if I have to) to essentially return a column of data that matches the original "location" column except in the one instance, and then map those values based upon the department column. 

 

How can I do this?

2 ACCEPTED SOLUTIONS
SteveHailey
Solution Specialist
Solution Specialist

I think you could create a new Custom Column in Power Query that looks something like below. Then remove your original location column. 

 

if [Location] <> "USA" then [Location]
else if [Department] = "Beach" then "Florida"
else if [Department] = "Bourbon" then "Kentucky"
else if [Department] = "Jazz" then "New Orleans"
else [Location]

 

In this example, "USA" is the one location that needs broken out. If the location is not USA, then it just returns the original location. If it is USA, then it checks the department column, and returns "Florida", "Kentucky", or "New Orleans", depending on the value in the department column.

 

-Steve

View solution in original post

Thanks Steve!

 

It appears that I now have two working options.  I also tried a conditional column that appears to be working for me given that the department values I am sorting on are unique to the new locations I want to create:

 

bdue_0-1640713411848.png

 

View solution in original post

3 REPLIES 3
SteveHailey
Solution Specialist
Solution Specialist

I think you could create a new Custom Column in Power Query that looks something like below. Then remove your original location column. 

 

if [Location] <> "USA" then [Location]
else if [Department] = "Beach" then "Florida"
else if [Department] = "Bourbon" then "Kentucky"
else if [Department] = "Jazz" then "New Orleans"
else [Location]

 

In this example, "USA" is the one location that needs broken out. If the location is not USA, then it just returns the original location. If it is USA, then it checks the department column, and returns "Florida", "Kentucky", or "New Orleans", depending on the value in the department column.

 

-Steve

Thanks Steve!

 

It appears that I now have two working options.  I also tried a conditional column that appears to be working for me given that the department values I am sorting on are unique to the new locations I want to create:

 

bdue_0-1640713411848.png

 

SteveHailey
Solution Specialist
Solution Specialist

Good deal, glad you have a working solution!

 

Just as an FYI, the code I pasted above is the same thing as a conditional column. In fact, if you paste that code to create a new Custom Column, and then later if you click the little gear icon next to that step in the Applied Steps pane, Power Query actually shows the graphical Conditional Column interface:

 

SteveHailey_0-1640714942189.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors