How to adjust grouped items by another field

[bdue]

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?

[SteveHailey · Accepted solution]

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

[SteveHailey · Accepted solution]

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

[bdue · Accepted solution]

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: