Forum Discussion
Power Query Text.insert with condition
Hello All,
I am trying to do a text.insert for a report only when a condition is satisfied. I am not at liberty to divulve any part of the actual data, so unfortunately I can't provide any sample data.
But basically, I would like to loop through the column "LC", check the length of the string for each row, if it is less than 3 then I will want to insert say 0 in postion 0 of the string, else I will keep the original string. Not sure if its a syntax problem or something else. Can anyone point me in the right dirrection?
= Table.TransformColumns(#"Changed Type", {{"LC", each if Text.Length([LC]) ❤️ then Text.Insert([LC],0, "0") else [LC], type text}})
Text.Length([LC]) should be Text.Length([LC]) < 3
and whereever you have used LC, replace it with _
_ Means current record which is [LC}
Hence, your formula would become
= Table.TransformColumns(#"Changed Type", {{"LC", each if Text.Length(_)<3 then Text.Insert(_,0, "0") else _, type text}})Also Text.Insert(_,0, "0") can be replaced with "0"&_ and also 1 { is sufficient, 2 { are not needed.
= Table.TransformColumns(#"Changed Type", {"LC", each if Text.Length(_)<3 then "0"&_ else _, type text})
2 Replies
Text.Length([LC]) should be Text.Length([LC]) < 3
and whereever you have used LC, replace it with _
_ Means current record which is [LC}
Hence, your formula would become
= Table.TransformColumns(#"Changed Type", {{"LC", each if Text.Length(_)<3 then Text.Insert(_,0, "0") else _, type text}})Also Text.Insert(_,0, "0") can be replaced with "0"&_ and also 1 { is sufficient, 2 { are not needed.
= Table.TransformColumns(#"Changed Type", {"LC", each if Text.Length(_)<3 then "0"&_ else _, type text})Thanks a bunch Vijay_A_Verma , that worked precisely.
