Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello,
I'm struggled with this replacement:
I have this table:
Original | TRY1 | TRY2 |
Non-qualified | Non-qualified | |
Non-qualified | Non-qualified | |
Non-qualified | Non-qualified | |
100000010,100000014 | Verify | 100000010,100000014 |
100000010,100000014 | Verify | 100000010,100000014 |
100000011 | Declared bankruptcy | Declared bankruptcy |
100000011 | Declared bankruptcy | Declared bankruptcy |
100000011 | Declared bankruptcy | Declared bankruptcy |
100000010 | International school | International school |
100000014 | Filantropic school | Filantropic school |
100000010 | International school | International school |
100000010,100000014 | Verify | 100000010,100000014 |
100000010,100000014 | Verify | 100000010,100000014 |
100000010,100000014 | Verify | 100000010,100000014 |
100000010,100000014 | Verify | 100000010,100000014 |
100000014 | Filantropic school | Filantropic school |
I have to create a column that replace all values for texts. The TRY1 I tried by Conditional Column, like
each if [Original] = "100000011", then "Declared bankruptcy",
else if [Original] = "100000014" then "Filantropic school"
...
else "Verify"
And in the TRY2 I tried by custom column, like
Table.AddColumn(#"Added Custom2", "Justificativa3", each Text.Combine(
List.ReplaceMatchingItems(
{[new_justificativa1]},
{
{"100000011", "Declared bankruptcy"},
{"100000014", "Filantropic school",
...
{null, "non-qualified"}
},
Comparer.OrdinalIgnoreCase)))
But it didn't work either.
Is there a way to create a column replacing all lines, including the ones with "100000010,100000014"? This is just an example, I have a lot of words to replace and if I create one step for each replacement, my pbix is going to explode..my table have more than two values in a row and more different values.
Best regards
Solved! Go to Solution.
Ah, I think the issue must be earlier then.
Try putting the null check in earlier:
Text.Combine(
List.Transform(
Text.Split(if [Original] = null then "" else [Original], ","),
each
if _ = "100000011" then "Declared bankruptcy"
else if _ = "100000014" then "Filantropic school"
else if _ = "" then "non-qualified"
else "Verify"
),
","
)
)
List.ReplaceMatchingItems is a good idea but it's not easy to have an else condition with that, so I'd suggest the following:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUlCK1UElDQ3AwNBAB8YyIUrckGieAQrPBI8cqW4gVRzIiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Original = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Original", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each
Text.Combine(
List.Transform(
Text.Split([Original], ","),
each
if _ = "100000011" then "Declared bankruptcy"
else if _ = "100000014" then "Filantropic school"
else if _ = "" then "non-qualified"
else "Verify"
),
","
)
)
in
#"Added Custom"
Thanks for your reply. It worked fo the lines with multiple itens, but it didn't work for the lines wich have "null" values. I'm sorry I miss specified instead of replace "" to "Non-qualified", I want to replace null to "Non-qualified". When I use your formula, I get error in null values. Is there a way to do this whitout adding more steps?
If you have actual nulls rather than empty strings, then @mussaenda's suggestion is correct.
Update the custom column definition to this:
if _ = null then "Non-qualified"
else if _ = "100000011" then "Declared bankruptcy"
else if _ = "100000014" then "Filantropic school"
else "Verify"
It didn't work either
It shows
"Expression.Error: We cannot convert the value null to type Text.
Details:
Value=
Type=[Type]"
Ah, I think the issue must be earlier then.
Try putting the null check in earlier:
Text.Combine(
List.Transform(
Text.Split(if [Original] = null then "" else [Original], ","),
each
if _ = "100000011" then "Declared bankruptcy"
else if _ = "100000014" then "Filantropic school"
else if _ = "" then "non-qualified"
else "Verify"
),
","
)
)
This one works very well. Thank you!
Hi @massotebernoull ,
from the reply of @AlexisOlson ,
You can replace "" to null and position it as the first argument.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
76 | |
74 | |
55 | |
45 |
User | Count |
---|---|
117 | |
106 | |
77 | |
66 | |
65 |