Forum Discussion
How to adjust grouped items by another field
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:
Solution SpecialistI 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:
- SteveHailey
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: