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
Anonymous
Not applicable

Replacing values in a column based on a second column

Hi everyone, 
I am stuck with the following problem.

I have a User table in the DB where I have, among the many users listed, two that have the same name. They are two different persons with two different IDs but the name is the same. The data is structured in this way:

IDUSERNAME
100Alpha
101Beta
102Beta
103Gamma


What I want to do is replace the two "Beta" users with two different names. One should be something like Beta-1 and the other Beta-2 

The point is that I don't want to create another column in my table, because I have color conventions and many other report-level stuff based on this column and replacing it with a new one (where I could use an IF condition based on the ID) would break many things. The model I have is complex and it would be too much work. 

Is there a way I can replace those values with a new one in the same column based on the ID with custom M code, like a function?

Thanks for any help

1 ACCEPTED SOLUTION

@Anonymous

the final result of the query is what will get loaded into your model/report. So the report will not know what happened in between. As long as the final result of the query has the same  structure as what you had in the beginning (with columns ID and USERNAME), there's no reason for anything to break. I would say create a new version (so that you have your current working version backed up) and try it.

In any case, i was just curious and built a version without creating an additional column. But the obvious, and simple, way to do it is what I described earlier.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRcswpyEhUitUB8Q2BfKfUEhjXCJVrDOS6J+bmAvmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, USERNAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"USERNAME", type text}}),
    step_= Table.TransformRows(#"Changed Type", each  if [USERNAME] = "Beta" then Record.TransformFields(_,{"USERNAME", (inner)=> [USERNAME] & "-" & [ID]}) else _),
    res_ = Table.FromRecords(step_)
in
    res_

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

 

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @Anonymous 

I'm not sure I understand.

1. What would be the problem with creating and additional cutom column, get there waht you need and then eliminating the original USERNAME column and rename the new one to USERNAME? The final resutl of the query would just have the same ID USERNAME columns.

2. Is "Beta" known in advance and can de hard-coded in the solution? OR should the solution look for duplicates?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

Anonymous
Not applicable

1 - So if I create a new column, delete the old one and then rename the new column as the first one nothing breaks in my report? All the slicers, drillthrough, color conventions etc. will keep working as if it was the same column? 

2- No, Beta can be hard coded. I have already found the user I need to "de-duplicate". No need to check for duplicates. I wanted to something like = if ID = 101, text.replace Beta with Beta1, if ID = 102, text.replace Beta with Beta2 (or something like this). Applying it on the same column

But if you tell me nothing will break I can also duplicate the column

@Anonymous

the final result of the query is what will get loaded into your model/report. So the report will not know what happened in between. As long as the final result of the query has the same  structure as what you had in the beginning (with columns ID and USERNAME), there's no reason for anything to break. I would say create a new version (so that you have your current working version backed up) and try it.

In any case, i was just curious and built a version without creating an additional column. But the obvious, and simple, way to do it is what I described earlier.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwUNJRcswpyEhUitUB8Q2BfKfUEhjXCJVrDOS6J+bmAvmxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, USERNAME = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"USERNAME", type text}}),
    step_= Table.TransformRows(#"Changed Type", each  if [USERNAME] = "Beta" then Record.TransformFields(_,{"USERNAME", (inner)=> [USERNAME] & "-" & [ID]}) else _),
    res_ = Table.FromRecords(step_)
in
    res_

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

SU18_powerbi_badge

 

Anonymous
Not applicable

Thanks!! I'l try with the query step as it looks like a more "elegant" solution but it's good to know that I could also create a new column and rebuild the structure in order to avoid breaking the report.

Thanks for your help!!

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.

Top Solution Authors
Top Kudoed Authors