Forum Discussion

avalonds's avatar
avalonds
Frequent Visitor
8 years ago
Solved

M language to transform column

I have the following column in powerquery and what I need to do is the following:

 

1. Ignore Trevor Noah and Unassigned

2. Replace Derek Fisher with "Unassigned"

3. For all other rows which start with a number extract the first three characters.

 

Variable Column:

432 Dakarta

312 London

833 Bob

Trevor Noah

Unassigned

Derek Fisher

 

I want the column to transform to this in Power Query using M:

Variable Column:

432

312

833

Trevor Noah

Unassigned

Unassigned

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi avalonds,

     

    You can try to use below query formula to achieve your requirement:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2UnBJzE4sKklUitWJVjI2NFLwyc9Lyc8Dcy2MjRWc8pPA7JCi1LL8IgW//MQMMD80L7G4ODM9LzUFzHVJLUrNVnDLLM5ILVKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Variable Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variable Column", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Transform", each if [Variable Column]= "Trevor Noah" or [Variable Column]= "Unassigned" then [Variable Column] else if [Variable Column]="Derek Fisher" then "Unassigned" else if Value.Is(Number.FromText(Text.Start([Variable Column],3)), type number) then Text.Start([Variable Column],3) else [Variable Column])
in
    #"Added Custom"

     

    Regards,
    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi avalonds,

     

    You can try to use below query formula to achieve your requirement:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE2UnBJzE4sKklUitWJVjI2NFLwyc9Lyc8Dcy2MjRWc8pPA7JCi1LL8IgW//MQMMD80L7G4ODM9LzUFzHVJLUrNVnDLLM5ILVKKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Variable Column" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Variable Column", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Transform", each if [Variable Column]= "Trevor Noah" or [Variable Column]= "Unassigned" then [Variable Column] else if [Variable Column]="Derek Fisher" then "Unassigned" else if Value.Is(Number.FromText(Text.Start([Variable Column],3)), type number) then Text.Start([Variable Column],3) else [Variable Column])
in
    #"Added Custom"

     

    Regards,
    Xiaoxin Sheng

    • avalonds's avatar
      avalonds
      Frequent Visitor
      8 years ago

      Thanks. Quick question. Is there any way I can use a Table.Transform Function?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi avalonds,

         

        Actually, I only add a custom column with if statement formula to achieve your requirement. I haven't use table.Transform function in above query.

         

        If you mean custom step, you can click on fx button to instead new custom steps.

        Edit query step settings (Power Query)

         

        Regards,

        Xiaoxin Sheng