Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JayKobbieJnr
Regular Visitor

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}})

 

 

 

 

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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})

View solution in original post

2 REPLIES 2
JayKobbieJnr
Regular Visitor

Thanks a bunch @Vijay_A_Verma , that worked precisely. 

Vijay_A_Verma
Super User
Super User

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})

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors