Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
ID | USERNAME |
100 | Alpha |
101 | Beta |
102 | Beta |
103 | Gamma |
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
Solved! Go to 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
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
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
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!!