Forum Discussion

ryanb11's avatar
ryanb11
Helper III
5 years ago
Solved

power bi custom column syntax

I have the below custom column in the query editor, where the source is a table with text data and then 2 columns are created upon this.   I need to change the source to the table 'rel' and the col...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi ryanb11 ,

    Please check if the below screen shot is what you want? If yes, please update it in Power Query Editor.

    1. Choose the related query and right click to choose "Rename" option, rename the query name as "rel"

    2. Navigate to Home ribbon, click "Advanced Editor". Copy and paste the below codes into the opening window:

    let
  Source = #table(
      {"Score"},
      List.Zip(
          {{
              "Call me on 08588812885",
              "Call me on 07525812845",
              "I need assitance please call me",
              "Last tried in 2019"
            }}
        )
    ),
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Score], " ")),
  #"Added Custom1" = Table.AddColumn(
      #"Added Custom",
      "Custom.1",
      each Text.Combine(
          List.Select(
              [Custom],
              (l) => [
                        ListOfCharacters = Text.ToList(l),
                        Result = not (List.Count(ListOfCharacters) >= 10
                                    and List.AllTrue(
                                            List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
                                    )
                    )][Result]
            ), " ")
    )
in
    #"Added Custom1"

    Best Regards

    Rena

amitjzaveri's avatar
amitjzaveri
Resolver II
5 years ago

ryanb11  It seems you're trying to use a source from a separate table in the same model and it is not getting referred. If that is the case then you will have to duplicate that table and write follow up transformation lines. 

 

Otherwise if you can share some screenshot then it would help to clear up things.

 

Did I answer your question? Mark my post as a solution!

ryanb11's avatar
ryanb11
Helper III
5 years ago

I have told you the source table so whty are you unable to help me with the syntax?

 

I cannot send screenshots of sensitive data.

 

Please can you help with the syntax from the 'rel' table I have in power bi.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi ryanb11 ,

    Please check if the below screen shot is what you want? If yes, please update it in Power Query Editor.

    1. Choose the related query and right click to choose "Rename" option, rename the query name as "rel"

    2. Navigate to Home ribbon, click "Advanced Editor". Copy and paste the below codes into the opening window:

    let
  Source = #table(
      {"Score"},
      List.Zip(
          {{
              "Call me on 08588812885",
              "Call me on 07525812845",
              "I need assitance please call me",
              "Last tried in 2019"
            }}
        )
    ),
  #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Split([Score], " ")),
  #"Added Custom1" = Table.AddColumn(
      #"Added Custom",
      "Custom.1",
      each Text.Combine(
          List.Select(
              [Custom],
              (l) => [
                        ListOfCharacters = Text.ToList(l),
                        Result = not (List.Count(ListOfCharacters) >= 10
                                    and List.AllTrue(
                                            List.Transform(ListOfCharacters, (x) => List.Contains({"0".."9"}, x))
                                    )
                    )][Result]
            ), " ")
    )
in
    #"Added Custom1"

    Best Regards

    Rena