Forum Discussion

QELucas's avatar
QELucas
New Member
1 year ago
Solved

Adding Text to data column based on text length

Hello,

I'm still fairly new to Power BI and I'm trying to add a new column thats based on the Tracking column.

Basically I'm not sure how to properly code the following:

If the length is =6 I would like to add "A1S1"

If the length is =8 add "S1"

If the length is =10 keep as is

 

I normally do this in an offline Excel doc which I use a simple If/Then and Concat but with Power BI having a different syntax I'm not sure how to proceed.

 

I should also mention that the excel doc is pulled from our ERP system which makes it not possible to add this formula to the excel doc.

 

Thank you!

Lucas

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi,Greg_Deckler .thanks for your concern about this issue.

    Your answer is excellent!
    And I would like to share some additional solutions below.
    Hello,QELucas .I am glad to help you.
    I hope you find my test below helpful, I have uploaded my test file so you can download it and see the steps in detail.
    Here is my test data.


    Use M code
    In Power Query:
     

    if Text.Length([Tracking])=6 then [Tracking] &"A1S1"
else if Text.Length([Tracking])=8 then [Tracking] &"S1"
else if Text.Length([Tracking])=10 then [Tracking]
else [Tracking]&"Error"

    Use DAX code
    In Power BI

    use IF function or SWITCH function:

    Create a calculate column:

    C_RFT Tracking = 
VAR LengthOfTarcking = LEN([Tracking])
RETURN
    IF(
        LengthOfTarcking = 6, 
        [Tracking] & "A1S1",
        IF(
            LengthOfTarcking = 8,
            [Tracking] & "S1",
            IF(
                LengthOfTarcking = 10,
                [Tracking],
                [Tracking] & "Error"
            )
        )
    )

     

    C_switch Tracking = 
VAR LengthOfTarcking = LEN([Tracking])
RETURN
SWITCH(TRUE(),
LengthOfTarcking =6,  [Tracking] & "A1S1",
LengthOfTarcking = 8,[Tracking] & "S1",
LengthOfTarcking = 10,[Tracking],
[Tracking] & "Error"
)

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

    QELucas Why is this posted like 6 or 7 times? 

     

    Sorry, having trouble following, can you post sample data as text and expected output?
    Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

    Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    The most important parts are:
    1. Sample data as text, use the table tool in the editing bar
    2. Expected output from sample data
    3. Explanation in words of how to get from 1. to 2.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,Greg_Deckler .thanks for your concern about this issue.

    Your answer is excellent!
    And I would like to share some additional solutions below.
    Hello,QELucas .I am glad to help you.
    I hope you find my test below helpful, I have uploaded my test file so you can download it and see the steps in detail.
    Here is my test data.


    Use M code
    In Power Query:
     

    if Text.Length([Tracking])=6 then [Tracking] &"A1S1"
else if Text.Length([Tracking])=8 then [Tracking] &"S1"
else if Text.Length([Tracking])=10 then [Tracking]
else [Tracking]&"Error"

    Use DAX code
    In Power BI

    use IF function or SWITCH function:

    Create a calculate column:

    C_RFT Tracking = 
VAR LengthOfTarcking = LEN([Tracking])
RETURN
    IF(
        LengthOfTarcking = 6, 
        [Tracking] & "A1S1",
        IF(
            LengthOfTarcking = 8,
            [Tracking] & "S1",
            IF(
                LengthOfTarcking = 10,
                [Tracking],
                [Tracking] & "Error"
            )
        )
    )

     

    C_switch Tracking = 
VAR LengthOfTarcking = LEN([Tracking])
RETURN
SWITCH(TRUE(),
LengthOfTarcking =6,  [Tracking] & "A1S1",
LengthOfTarcking = 8,[Tracking] & "S1",
LengthOfTarcking = 10,[Tracking],
[Tracking] & "Error"
)

     

    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

    Best Regards,

    Carson Jian,

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.