Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Column split by conditon

I have a requirement to extract/split data if value starts with P or QM to be 7 characters rest should be as-is, how it can be achived? help needed As-is PABCP01A CSOMTO04 PPQRX01M QM0888TA ATL...
Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    7 years ago

    Anonymous -

    Are you meaning that you have so many conditions for Text.StartsWith = "P" || "QM" || etc.?

     

    If so, then you should include those requirements in your question.

     

    Otherwise, you just need to add the logic from the last statment 

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") then Text.RemoveRange([#"As-is"],7) else if Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"])
ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
7 years ago

Anonymous -

This seems to work:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnB0cg4wMHRUitWJVnIO9vcN8TcwAXMCAgKDIgwMfcGcQF8DCwuLEIgyxxAf5xAfA0Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"As-is" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"As-is", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") then Text.RemoveRange([#"As-is"],7) else if Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"])
in
    #"Added Custom"
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    ChrisMendoza i have many such values below is the sample

     

    • ChrisMendoza's avatar
      ChrisMendoza
      Resident Rockstar
      7 years ago

      Anonymous -

      Are you meaning that you have so many conditions for Text.StartsWith = "P" || "QM" || etc.?

       

      If so, then you should include those requirements in your question.

       

      Otherwise, you just need to add the logic from the last statment 

      #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") then Text.RemoveRange([#"As-is"],7) else if Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"])
    • dax's avatar
      dax
      Community Support
      7 years ago

      Hi ajitk15, 

      You could follow above ChrisMendoza's suggestions or simplify code by "Or " condition like below

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCnB0cg4wMHRUitWJVnIO9vcN8TcwAXMCAgKDIgwMfcGcQF8DCwuLEIgyxxAf5xAfA0Ol2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"As-is" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"As-is", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.StartsWith([#"As-is"], "P") or Text.StartsWith([#"As-is"],"QM") then Text.RemoveRange([#"As-is"],7) else [#"As-is"])
in
    #"Added Custom"

      Best Regards,
      Zoe Zhi

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